Using Custom Properties Inside LINQ to SQL Queries

Add to FacebookAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to TwitterAdd to TechnoratiAdd to Yahoo BuzzAdd to Newsvine

One thing that initially caused me some trouble with Linq to SQL was that properties
and functions defined on the object cannot be translated to SQL. For example, this code throws an
“The member ‘Table1.DisplayName’ has no supported translation to SQL.” exception when executed.

public string DisplayName
   {
      get
      {
         return this.NameFirst + " " + this.NameLast;
      }
}

public static IQueryable<Table1> GetAll()
{
   return (from t in DataContext.Table1s
             select t).OrderBy(t => t.DisplayName);
}

One simple solution is to just define your sort option each time it needs used.

public static IQueryable<Table1> GetAll()
{
   return (from t in DataContext.Table1s
             select t).OrderBy(t => t.NameFirst + " " + t.NameLast);
}

This option is pretty tedious to write and maintain, especially if you’re going to be using the function often.

Another option is to define a System.Linq.Expression, which is a lambda expression that can be converted to SQL.

static Expression<Func<Table1, string>> DisplayNameExpr = t => t.NameFirst + " " + t.NameLast;

public static IQueryable<Table1> GetAll()
{
   return (from t in DataContext.Table1s
             select t).OrderBy(Table1.DisplayNameExpr);
}

Note that if you do choose this way, the “DisplayName” is essentially defined in two different places. To solve this problem, use the expression to define the property.

{
   get
   {
      var nameFunc = DisplayNameExpr.Compile(); // compile the expression into a Function
      return nameFunc(this); // call the function using the current object
   }
}

Note this method can also be used to define functions that accept arguments,  so the following code also works as expected.

static Expression<Func<Table1, bool>> HasReq(int numReq)
{
   return (t => (t.IntItem + t.AnotherInt) > numReq);
}

public static IQueryable<Table1> GetWithReq(int req)
{
   return (from t in DataContext.Table1s
             select t).Where(Table1.HasReq(req));
}
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 &amp;&amp; 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.