Linq for search queries with multiple optional parameters

Several months ago, I began using Linq on one of our major projects.  Initially, I didn’t really understand how beneficial it would be, but perhaps that was a good lesson to give new things a fair shake before discounting them.  After all, there was probably a good reason for creating it, or they wouldn’t have gone to the trouble.  Anyway, I have been very impressed, to say the least.  The speed with which I can create new queries to get the data that I need is remarkable, indeed.

Reflecting now on what we used to do, I can’t help but quote the old Billy Joel song, “The good old days weren’t always good, and tomorrow ain’t as bad as it seems.”

Invariably, there comes a point where we needed to add a column to a table and then regenerate the Dataset queries.  Most of the time, this was pretty easy, but every once in a while, one of the regenerated queries decided to reorder its parameters, unbeknownst to us.  On a good day, this became immediately obvious because the parameter types were different.  On a bad day, however, which became a bad day even it was a good day, the parameters types would be the same and no compile error would alert us to what had happened.  Those were sometimes difficult to even notice and then even more difficult to find.

Then you had the common search page, where the user may or may not specifiy a myriad of various search items.  This gave rise to either one heck of a Sql query with multiple tests for -1 or a just a lot of individual queries.  Neither option was very pleasant.

SELECT *
FROM ThisTable
WHERE  ((ThisColumn = @ThisColumn) || (ThisColumn = -1))
AND ((ThatColumn = @ThatColumn) || (ThatColumn = -1))
AND (IAmGoingInsane = true)

Of course, this gave rise to major efficiency issues as well in the speed of the search.

Enter Linq. (applause)

This seems obvious to me now, but it didn’t occur to me at first.  One of the nice things about Linq is that it builds the query in the code and doesn’t execute it until you, so you can basically add only those parameters that the user has selected, like this:

var results = (from theStuff in DataContext.Stuffs
where theStuff.NameLast == nameLast);
if (nameFirst != "")
results = results.Where(r => r.NameFirst == nameFirst);
if (city!= "")
        results = results.Where(r => r.City == city);
return results;

Since the code only adds the parameters you need, the Sql query can still be optimized, and you don’t need a bunch of
different queries–just one, with all the parameters in it.

Pretty nice. I’m very happy, indeed, that the good old days of DataSets are gone. Thanks, Linq!

Advertisements

LINQ to SQL DataContext Initializer

In LINQ to SQL, you design a model by dropping tables from your DB onto a designer surface.  The model creates a DataContext, which allows you to reference your tables in the model as (something like) a collection you can query with LINQ.  You can also call SubmitChanges() on the DataContext to persist any local changes you made back to the database.

One way to do this in the webapp would be something like:

var datacontext = new ModelDataContext();
var user = (from u in datacontext.Users where u.UserID==5 select u).First();
user.Email = "newemail@email.com";
datacontext.SubmitChanges();

This isn’t ideal, because you are peppering your webapp with data/business logic – just because you need to keep an instance of a datacontext. You could pull out the data logic, but you’d still need to new up an instance of the data context and pass it to each business logic function – that would be a mess. I’d prefer the webapp to not know/care about persistence or a DataContext at all. It would be great if the Business logic classes could handle that.

The problem is, lots of the business logic uses a static method ( User.Add(“username”,”password”) – you shouldn’t need an instance of User to run that, it works like a Factory pattern and returns an instance). A local DataContext instance would have to be static to be accessible to those functions – and ASP.NET treats local statics something like global/application variables. One instance would be shared across every request and every user on the site. DataContext instances are supposed to be available for limited amount of time – reusing one in this way would create all kinds of weird issues under any kind of load.

So, what I needed was somewhere to store the instance – shared across multiple business logic calls but scoped to exactly one request. After some trial and error, I ended up storing it in different locations for ASPX pages, for ASMX webservices (standard, and WSE 3.0), and for non-ASP.NET uses (particularly LinqPad). First, I used a interface to implement for each type and cast to get the DataContext:

public interface IDataContextHelper
{
	ModelDataContext DataContext { get; }
}

Next, I implemented that handler for the special page class that all ASPX pages use:

public class FoliotekPage : System.Web.UI.Page, Foliotek.DataAccess.IDataContextHelper
{
.
.
.

	private Foliotek.DataAccess.ModelDataContext dataContext = null; //local and non-static, so scoped with the page handler
	public Foliotek.DataAccess.ModelDataContext DataContext
	{
		get
		{
			if (dataContext == null)
			{
				dataContext = new Foliotek.DataAccess.ModelDataContext();

			}
			return dataContext;
		}
	}
.
.
.
}

For various reasons, I didn’t have access to the CurrentHandler to do this for webservices and ashx files. Instead, I used the Items collection on the ASHX handler class, and the .Current[] collection on the SoapContext classes:

Standard webservice:

public class AjaxSupport : System.Web.Services.WebService
{

	public AjaxSupport()
	{
		this.Context.Items["ModelDataContext"] = new Foliotek.DataAccess.ModelDataContext();
	}
.
.
.
}

WSE 3.0 webservice (security object)

SoapContext.Current["ModelDataContext"] = new dac.ModelDataContext();

Finally, I tied it all together by writing a property in the super class for all of my business logic classes:

static ModelDataContext datacontext;
internal static ModelDataContext DataContext
{
	get
	{

		if (System.Web.HttpContext.Current == null) // for linqpad/etc
		{
			if (datacontext == null)
				datacontext = new ModelDataContext();
			return datacontext;
		}
		else if (System.Web.HttpContext.Current.CurrentHandler is IDataContextHelper) // for web page
		{
			return ((IDataContextHelper)System.Web.HttpContext.Current.CurrentHandler).DataContext;
		}
		else if (System.Web.HttpContext.Current.Items != null && System.Web.HttpContext.Current.Items["ModelDataContext"] != null) // for simple webservice
		{
			return (ModelDataContext)System.Web.HttpContext.Current.Items["ModelDataContext"];
		}
		else if (Microsoft.Web.Services3.SoapContext.Current != null) // for webservices (in auth)
		{
			return (ModelDataContext)Microsoft.Web.Services3.SoapContext.Current["ModelDataContext"];
		}
		else if (Microsoft.Web.Services3.RequestSoapContext.Current != null) // for webservices (in webservice call)
		{
			return (ModelDataContext)Microsoft.Web.Services3.RequestSoapContext.Current["ModelDataContext"];
		}
		else
		{ // make sure we know about it if something uses the static context, that can be bad under load...
			ErrorLog.Add(-1, -1, -1, "Warning - Using Static Data Context","",
			"", "", 80, System.Web.HttpContext.Current.Request.Url+"", "", "", new 	Foliotek.DataAccess.ErrorLog.QueryValueDataTable(), "",
			"", "", "", "", "");
			if (datacontext == null)
				datacontext = new ModelDataContext();
			return datacontext;
		}
	}
}

I think the methodology is sound. I happened upon the Items collection on HttpContext fairly recently – its possible that is a solution that would cover all of the ASP.NET cases. You might even be able to instantiate it on Application_BeginRequest and clear it on Application_EndRequest and clear out some of the extra code. Here’s the resulting code:

Web App:

var user = Business.User.Get(userid);
user.Email = "newemail@email.com";
user.Update();

Business Class:

public static User Get(int userid)
{
	return (from u in DataContext.Users
	where u.UserID == userid
	select u).First();
}

public void Update()
{
	DataContext.SubmitChanges();
}

So, all that work had 2 main benefits: the web app developer doesn’t have to think about persistence at all, and the business logic developer doesn’t have to deal with managing a DataContext instance. Less than 100 lines of code eliminated several hundred lines of boiler plate code.