Automatic Update of Datamodel In Linq

One of the issues that we came across fairly quickly when converting some projects to Linq was how unusable the default Linq to SQL utility was. The interface worked extremely well for 2 or 3 tables, but quickly became unmanageable with much more than that.

We began looking for other solutions, and discovered a command line utility called SQLMetal that can be used to generate a DataModel from a connection string and/or an XML file.

The solution we settled on uses SQLMetal to generate XML markup from the database, then uses XSL Transformations to make desired property/model changes (Private properties, Delay loaded, etc), and then uses SQLMetal to generate a code file from this XML file.

To start, we created a batch file called updateModel.bat and placed it in the project:

"C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\SQLMetal.exe" /conn:"CONNECTIONSTRING" 
      /timeout:0 /namespace:MODELNAMESPACE /context:DATACONTEXTNAME /language:csharp /pluralize 
      /dbml:"%~dp0DataModel.dbml"
"%~msxsl.exe" "%~dp0DataModel.dbml" "%~dp0ModifyDbml.xslt" -o "%~dp0DataModel.dbml"
"C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\SQLMetal.exe" /code:"%~dp0DataModel.cs"
    /namespace:MODELNAMESPACE /context:DATACONTEXTNAME /language:csharp 
    /pluralize %DataModel.dbml

The output of SQLMetal in the first line of this file is an XML file called DataModel.dbml that looks something like this :

  <Table Name="dbo.Person" Member="Persons">
    <Type Name="Person">
      <Column Name="PersonID" Type="System.Int32" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false"></Column>
      <Column Name="AddressID" Type="System.Int32" DbType="Int NOT NULL" CanBeNull="false"></Column>
      <Column Name="Email" Type="System.String" DbType="VarChar(255) NOT NULL" CanBeNull="false"></Column
      <Column Name="NameFirst" Type="System.String" DbType="VarChar(255) NOT NULL" CanBeNull="false"></Column>
      <Column Name="NameLast" Type="System.String" DbType="VarChar(255) NOT NULL" CanBeNull="false"></Column>
<Association Name="FK_Person_Address" Member="Address" ThisKey="AddressID" OtherKey="AddressID" Type="Address" IsForeignKey="true"></Association>
    </Type>
  </Table>

The second line of this script uses a utility called msxsl.exe (note that this requires MSXML). This program uses a file called ModifyDbml.xslt to perform an XSL tranformation on the DataModel.dbml file.

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0"
								xmlns:dbml="http://schemas.microsoft.com/linqtosql/dbml/2007"
								xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
								xmlns:msxsl="urn:schemas-microsoft-com:xslt"
								exclude-result-prefixes="msxsl">
  <xsl:output method="xml" indent="yes"/>

  <xsl:template match="dbml:Database/dbml:Table/dbml:Type[@Name = 'Person']/dbml:Column[@Name = 'AddressID']">
    <xsl:copy>
      <xsl:apply-templates select="@* | node()" />
       <xsl:attribute name="AccessModifier">Private</xsl:attribute>
    </xsl:copy>
  </xsl:template>

<xsl:template match="@* | node()">
     <xsl:copy>
      <xsl:apply-templates select="@* | node()"/>
    </xsl:copy>
  </xsl:template>
</xsl:stylesheet>

This will make it so the “AddressID” property of “Person” is a private property – and this is where all such DataModel changes should be stored. Note that any changes made directly to the DataModel after this point will be lost each time the files are generated.

The final line of this script generates a DataModel.cs file from the updated XML file.

Finally, we looked for a way to call this script to update from within visual studio. To do this, we went to Tools -> External Tools -> Add, and used the following arguments:

This allows you to highlight the updateModel.bat from Visual Studio and go to “Tools->CMD Prompt” to update your DataModel.

Advertisements

Using the Web.Config connection string with LINQ to SQL

When updating a project to use LINQ to SQL, I found an issue with deploying to multiple environments.  Each environment (development, staging, live) had its’ own database associated with this.  Since I had the .dbml in another assembly, it was only reading from the app.config in the assembly it resided in.  I was storing the database connection string in the web.config of the project so I thought it would be nice to just use that instead of the app.config.

The first thing I needed to do was to keep the .dbml file from reading from the app.config.  After opening up the .dbml file, I opened the properties window for the file.  In the properties window, there is a setting for “Connection”.  In the “Connection” dropdown I selected the “(None)” selection.  That keeps the .dbml file from accessing the app.config for the database connection string.



The "Connection" setting in the .dbml Properties

Now I needed to get my MainDataContext to use the Web.Config connection string.  For this I created a partial class for my MainDataContext and created a constructor that passed the connection string from the Web.Config.

public partial class MainDataContext
{
    public MainDataContext()
        : base(System.Configuration.ConfigurationManager.ConnectionStrings["Database.connection.string.from.web.config"].ToString(), mappingSource)
    {
        OnCreated();
    }
}

Now when I deploy to different environments the .dbml file is accessing the correct database instead of the same one from the app.config.

Running a scheduled task inside of a asp.net web application

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

Occasionally, there might be some activity in your web application that should trigger the need for some code to execute at a later date. One of the most common cases is sending an email (reminder, change digest, etc), but there are other uses too. You might want to defer some processing intensive activity to off hours. In my most recent case, I needed to check for changes to an purchased/installed application’s db and replicate them to another product every 5 minutes or so.

I’ve solved similar problems before by:

  1. From the web application, inserting a row in a ‘scheduled tasks’ database with a time to execute and a script url to run
  2. Creating an running a windows service somewhere that wakes up every 5 minutes or so, looks at the table for ‘due’ items, and opens a request to the url

This works, but it has some drawbacks.

  • You have to learn how to build and deploy a service.  Not particularly hard, but something a web developer doesn’t really need to know
  • You have to copy or figure out how to share some data access logic between the service and the web application, and maintain changes
  • You have to figure out where to deploy the service
  • You have to have somewhere to deploy the service – if you are using a shared webhost with no RDP you are out of luck
  • It’s hard to be sure the service is running.  It’s easy to forget about if your infrastructure changes.
  • You need to deal with it when the service errors.
  • You have to be careful that the service doesn’t run the same script twice (or make it so it doesn’t hurt anything if it does), in case it gets run on two machines, etc.
  • Many more tiny, but real headaches for dealing with and maintaining a separate but connected project

I really didn’t want to go through all of that again for yet another project.  There had to be a simpler solution.

Thanks to Google, I found this article that led me to use a Cache object expiration to simulate the service.  It’s a hack, but it solved my issue.

Later, I found this StackOverflow post about the same issue/fix.  The comments led me to a System.Timers.Timer solution, which is easier to understand. Here it is:

The global.asax:

     public const int MINUTES_TO_WAIT = 5;

    private string _workerPageUrl = null;
    protected string WorkerPageUrl
    {
        get
        {
            if (_workerPageUrl == null)
                _workerPageUrl = (Application["WebRoot"] + VirtualPathUtility.ToAbsolute("~/DoTimedWork.ashx")).Replace("//", "/").Replace(":/", "://") + "?schedule=true";


            return _workerPageUrl;
        }
    }


    protected void Application_Start(Object sender, EventArgs e)
    {
        Uri reqUri = HttpContext.Current.Request.Url;
        Application["WebRoot"] = new UriBuilder(reqUri.Scheme, reqUri.Host, reqUri.Port).ToString();
        Application["TimerRunning"] = false;

        //StartTimer();   // don't want timer to start unless we call the url (don't run in dev, etc).  Url will be called by montastic for live site.
    }

    private void StartTimer()
    {
        if (!(bool)Application["TimerRunning"]) // don't want multiple timers
        {
            System.Timers.Timer timer = new System.Timers.Timer(MINUTES_TO_WAIT * 60 * 1000);
            timer.AutoReset = false;
            timer.Enabled = true;
            timer.Elapsed += new System.Timers.ElapsedEventHandler(timer_Elapsed);
            timer.Start();
            Application["TimerRunning"] = true;
        }

    }

    void timer_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
    {
        Application["TimerRunning"] = false;
        System.Net.WebClient client = new System.Net.WebClient();
        // have to issue a request so that there is a context
        // also lets us separate all of the scheduling logic from the work logic
        client.DownloadData(WorkerPageUrl + "&LastSuccessfulRun=" + Server.UrlEncode((CurrentSetting.LastSuccessfulRun ?? DateTime.Now.AddYears(-1)).ToString()));
    }

    protected void Application_BeginRequest(Object sender, EventArgs e)
    {
        if (HttpContext.Current.Request.Path == GetLocalUrl(new Uri(WorkerPageUrl)))
        {
            CurrentSetting.LastRun = DateTime.Now;
            try
            {
                CurrentSetting.RunCount++;
            }
            catch (Exception)
            {
                CurrentSetting.RunCount = 0;  // just in case of an overflow
            }
            SaveSettings();
        }
    }
    protected void Application_EndRequest(Object sender, EventArgs e)
    {
        if (HttpContext.Current.Request.Path == GetLocalUrl(new Uri(WorkerPageUrl)))
        {
            if (HttpContext.Current.Error == null) //
            {
                CurrentSetting.LastSuccessfulRun = DateTime.Now;
                SaveSettings();
            }

            if (HttpContext.Current.Request["schedule"] == "true")// register the next iteration whenever worker finished
                StartTimer();
        }
    }

    void Application_Error(object sender, EventArgs e)
    {
        if (HttpContext.Current.Request.Path == GetLocalUrl(new Uri(WorkerPageUrl)))
        {
            Common.LogException(HttpContext.Current.Error.GetBaseException());
        }

    }
    
    protected class Setting
    {
        public DateTime? LastRun { get; set; }
        public DateTime? LastSuccessfulRun { get; set; }
        public long RunCount { get; set; }
    }
    Setting currentSetting = null;
    protected Setting CurrentSetting
    {
        get
        {
            if (currentSetting == null)
            {
                using (System.Security.Principal.WindowsImpersonationContext imp = Common.Impersonate())
                {
                    System.IO.FileInfo f = new System.IO.FileInfo(HttpContext.Current.Server.MapPath("~/data/settings.xml"));
                    if (f.Exists)
                    {
                        System.Xml.Linq.XDocument doc = System.Xml.Linq.XDocument.Load(f.FullName);
                        currentSetting = (from s in doc.Elements("Setting")
                                          select new Setting()
                                          {
                                              LastRun = DateTime.Parse(s.Element("LastRun").Value),
                                              LastSuccessfulRun = DateTime.Parse(s.Element("LastSuccessfulRun").Value),
                                              RunCount = long.Parse(s.Element("RunCount").Value)
                                          }).First();

                    }
                }
            }

            if (currentSetting == null)
            {
                currentSetting = new Setting()
                {
                    LastRun = null,
                    LastSuccessfulRun = DateTime.Now.AddYears(-1),//ignore older than one year old in test
                    RunCount = 0
                };
            }

            return currentSetting;
        }
        set
        {
            currentSetting = value;
            if (Common.Live)
            {
                using (System.Security.Principal.WindowsImpersonationContext imp = Common.Impersonate())
                {
                    System.IO.DirectoryInfo di = new System.IO.DirectoryInfo(HttpContext.Current.Server.MapPath("~/data"));
                    if (!di.Exists)
                        di.Create();
                    System.Xml.XmlWriter writer = System.Xml.XmlWriter.Create(HttpContext.Current.Server.MapPath("~/data/settings.xml"));
                    try
                    {
                        System.Xml.Linq.XDocument doc = new System.Xml.Linq.XDocument(
                                new System.Xml.Linq.XElement("Setting",
                                    new System.Xml.Linq.XElement("LastRun", currentSetting.LastRun ?? DateTime.Now),
                                    new System.Xml.Linq.XElement("LastSuccessfulRun", currentSetting.LastSuccessfulRun),
                                    new System.Xml.Linq.XElement("RunCount", currentSetting.RunCount)
                                    )
                            );
                        doc.WriteTo(writer);
                    }
                    catch (Exception exc)
                    {
                        Common.LogException(exc);
                    }
                    finally
                    {
                        writer.Flush();
                        writer.Close();
                    }
                }
            }
        }
    }
    protected void SaveSettings()
    {
        CurrentSetting = CurrentSetting; // reset to ensure "setter" code saves to file
    }




 

    private string GetLocalUrl(Uri uri)
    {
        string ret = uri.PathAndQuery;
        if (uri.Query != null && uri.Query.Length>0)
            ret = ret.Replace(uri.Query, "");

        return ret;
    }

DoTimedWork.ashx:

 protected DateTime LastSuccessfulRun
    {
        get
        {
            try
            {
                return DateTime.Parse(HttpContext.Current.Request["LastSuccessfulRun"]);
            }
            catch (Exception) { }
            return DateTime.Now.AddDays(-1);
        }
    }
    
    public void ProcessRequest(HttpContext context)
    {
        if (context.Request["dowork"] != "false") // don't do work if it's just motastic hitting the page (to make sure the  timer is running)
        {
                context.Server.ScriptTimeout = 1800; // 30 minutes

                // do work
        }
        context.Response.Write("done");
    }
 
    public bool IsReusable {
        get {
            return false;
        }
    }

Common.cs

using System.Runtime.InteropServices;
using System.Security.Principal;

    public static  bool Live
    {
        get
        {
            return HttpContext.Current.Request.Url.Host != "localhost";
        }
    }
    public const int LOGON_TYPE_INTERACTIVE = 2;
    public const int LOGON_TYPE_PROVIDER_DEFAULT = 0;
    // Using this api to get an accessToken of specific Windows User by its user name and password
    [DllImport("advapi32.dll", CharSet = CharSet.Unicode, SetLastError = true)]
    static public extern bool LogonUser(string userName, string domain, string passWord, int logonType, int logonProvider, ref IntPtr accessToken);

    public static WindowsImpersonationContext Impersonate() //run code as a windows user with permissions to write files, etc.
    {

        IntPtr accessToken = IntPtr.Zero;
        LogonUser("REPLACE_WITH_WINDOWS_USER", "", "REPLACE_WITH_WINDOWS_PASSWORD", LOGON_TYPE_INTERACTIVE, LOGON_TYPE_PROVIDER_DEFAULT, ref accessToken);

        WindowsIdentity identity = new WindowsIdentity(accessToken);

        return identity.Impersonate();
    }
    public static void LogException(Exception exc)
    {
        LogActivity(exc.Message + "\n" + exc.StackTrace);
    }
    public static void LogActivity(string message)
    {
        if (Live)
        {
            using (WindowsImpersonationContext imp = Impersonate())
            {
                DirectoryInfo d = new DirectoryInfo(HttpContext.Current.Server.MapPath("~/data/temp/"));
                if (!d.Exists)
                    d.Create();
                var file = File.Create(HttpContext.Current.Server.MapPath("~/data/temp/" + DateTime.Now.Ticks + ".log"));
                try
                {
                    byte[] m = System.Text.Encoding.ASCII.GetBytes(message + "\n");
                    file.Write(m, 0, m.Length);
                }
                catch (Exception exc)
                {
                    byte[] m = System.Text.Encoding.ASCII.GetBytes(exc.Message + "\n" + exc.StackTrace);
                    try
                    {
                        file.Write(m, 0, m.Length);
                    }
                    catch (Exception) { }
                }
                finally
                {
                    file.Flush();
                    file.Close();
                }
            }
        }
        else
        {
            HttpContext.Current.Response.Write(message);
        }
    }

There are some issues with this approach to consider.

  • for very high usage sites or very intensive timed work, the work may put burden you wouldn’t want on your webserver
  • Application_Start only runs after the first request to your site after an app pool recycle, IIS restart, server restart, etc. If your site goes through periods of inactivity, you may or may not care if the Timer executes. If you do, you need to ensure the site is hit regularly in some way. I use the website monitor montastic for this.

So, there are going to be circumstances that make the windows service solution better. You just need to decide whether the benefits of using a service outweigh the pain of developing, maintaining, and deploying it alongside your web application.

Unexpected benefits of Precompilation of LINQ

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

I once had a manager who told me – I can solve any maintenance problem by adding a layer of abstraction.  I can solve any performance problem by removing a layer of abstraction.

I think LINQ to SQL is a wonderful way to abstract the persistence layer elegant, easy to use, easy to manipulate, and easy to maintain lines of code.  Instead of writing SQL which amounts to “how to retrieve” the data – you manipulate an expression tree that gets closer to specifying “what data I want”.  The upside of this is huge – you can change the expression tree at any level of your code, and let .NET decide how to best write the SQL at the last possible moment – which effectively gits rid of dealing with intermediate results and inefficiently written SQL.  Unfortunately, this abstraction does indeed cause a performance hit – the translation/compilation of the tree to SQL – and it’s probably much bigger than you would think.  See http://peterkellner.net/2009/05/06/linq-to-sql-slow-performance-compilequery-critical/ to see what I mean.  In my analysis (using ANTS Profiler), when using uncompiled LINQ – the performance hit is usually about 80% compilation and only 20% retrieving the data!  Thankfully, .NET does allow you to precompile a LINQ query and save the compilation to use over and over again.

Your natural tendency when hearing those kind of numbers might be to precompile every single LINQ query you write.  There’s a big downside to doing that, though – you lose the ability to manipulate the compiled query in other parts of your code.  Another downside is that the precompilation code itself is fairly ugly and hard to read/maintain.

I’m a big believer in avoiding “premature optimization”.  What happens if you precompile everything, and in a version or two Microsoft resolves the issue and caches compilations for you behind the scenes?  You have written a ton of ugly code that breaks a major benefit of LINQ to SQL and is totally unnecessary.

Instead, I recommend you go after the low hanging fruit first – precompile the most frequently accessed queries in your application and the ones that gain no benefit from manipulating the expression tree.  In the applications I work on – there is a perfect case that fits both of these – the “get” method that returns the LINQ object representation of a single row in the database.  These are hit quite often – and there is absolutely no case where the expression tree is further refined.

The old way it was written:

	public static Item Get(int itemid) {
		return (from i in DataContext.Items
			where i.ItemID == itemid
		select i).First();
	}

The new way with Precompiled LINQ:

	private static Func<ModelDataContext, int, Item>
		GetQuery = CompiledQuery.Compile(
			(ModelDataContext DataContext, int itemid) =>

				(from i in DataContext.Items
					where i.ItemID == itemid
				select i).First()

				);

	public static Item Get(int itemid) {
		return GetQuery.Invoke(DataContext,itemid);
	}

Applying this fairly simple change to the application, I’d estimate we got 80%+ of the benefits of compiled LINQ, at the expense of a few extra lines of code per object/table and absolutely no loss of the tree manipulation.

Linq Expressions on an Interface

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 I found myself doing quite a bit an often causing logic errors was comparing
dates in Linq queries. For example, if I want to get games that occur between a date range, I might be
doing something like this :

	public static IQueryable<Competition> GetBetweenDates(DateTime startDate, DateTime endDate)
	{
		return (from comp in DataContext.Competitions
				where comp.Date >= startDate && comp.Date <= endDate
				select comp);
	}

I then want to get a list of tournaments that occur between a date range, and I might
essentially rewrite this code using a couple different variables. Rather, I can implement
a common interface on these classes and have an expression applied to all classes that
implement this interface.

	public interface IDate
	{
		DateTime Date { get; set; }
	}
	
	public partial class Competition : DataBase, IDate
	{
		...
	}
	
	public static class Expressions
	{
        public static Expression<Func<T, bool>> GetBetweenDates<T>(DateTime start, DateTime end)
            where T : IDate
        {
            return (t => t.Date >= start && t.Date <= end);
        }	
	}	

Then, rather than rewriting this for each class with a date, I can just apply this expression
to my queries.

	public static IQueryable<Competition> GetBetweenDates(DateTime startDate, DateTime endDate)
	{
		return (from comp in DataContext.Competitions
			select comp).Where(
                             Expressions.GetBetweenDates<Competition>(startDate, endDate));
	}

Avoid static variables in ASP.NET

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

Occasionally, I like to write static methods on classes. They are useful whenever the method loosely relates to to the class – but it doesn’t involve a specific instance of the class.

A good example of a static method in the .NET library is the int.Parse(string val) method. This method basically reads a string and tries to return an integer representation. The method logically fits with the int Class (I suppose a string could have a ToInt() instance method…but that would be overwhelming as you added ToFloat(), ToDecimal(), To…()) – but when you run it, there’s no reason to have an instance of int already. You run it to create a new instance of int.

Commonly, I follow similar logic in the business layer of my webapps. I write a static Add(param1,….,paramx) method that returns an instance of the class after it is persisted to the database. I could accomplish this other ways, but I think the resulting application code reads better like:

      User.Add(username,password);

Than:

      new User(username,password).Add();

or, worse:

      DataContext.Users.InsertOnSubmit(new User(username,password));
      DataContext.SubmitChanges();

The issue with static methods in your business logic is that you often need a common object to talk to the database through: a SqlConnection, a TableAdapter, A LINQ DataContext, etc. I could certainly create those objects locally inside each static method, but that’s time consuming and hard to maintain. I want instead to define a common property (in the business class or a parent class of it) that lazy-initializes and returns an instance of the object when I need it. The problem is that the property must also be static for a static method to have access to it.

The easiest way to accomplish this is something like:

      private static ModelDataContext dataContext=null;
      protected static ModelDataContext DataContext
      {
            get
            {
                 if(dataContext==null)
                     dataContext = new ModelDataContext();
                 return dataContext;
             }
       }

The tricky thing is that this will probably work in development and testing, until you get some load on your website. Then, you’ll start seeing all kinds of weird issues that may not even point to this code as the problem.

Why is this an issue? It’s all about how static variables are scoped inside a ASP.NET application. Most web programmers think of each page in their application as its own program. You have to manually share data between pages when you need to. So, they incorrectly assume that static variables are somehow tied to a web request or page in their application. This is totally wrong.

Really, your whole website is a single application, which spawns threads to deal with requests, and requests are dealt with by the code on the appropriate page. Think of a page in your webapp as a method inside of one big application, and not an application of its own – a method which is called by the url your visitor requests.

Why does this matter? Because static variables are not tied to any specific instance of any specific class, they must be created in the entire application’s scope. Effectively, ASP.NET static variables are the same as the global variables that all your programming teachers warned you about.

That means that, for the property above, every single request/page/user of your website will reuse the first created instance of DataContext created. That’s bad for several reasons. LINQ DataContexts cache some of the data and changes you make – you can quickly eat up memory if each instance isn’t disposed fairly quickly. TableAdapters hold open SQLConnections for reuse – so if you use enough classes of TableAdapters, you can have enough different static vars to tie up all of your db connections. Because requests can happen simultaneously, you can also end up with lots of locking/competing accesses to the variable. Etc.

What should you do about it? In my case, I take advantage of static properties that reference collections that are scoped to some appropriately short-lived object for my storage. For instance, System.Web.HttpContext.Current.Items:

      protected static ModelDataContext DataContext
      {
            get
            {
                 if(System.Web.HttpContext.Current.Items["ModelDataContext"]==null)
                     System.Web.HttpContext.Current.Items["ModelDataContext"] = new ModelDataContext();
                 return (ModelDataContext)System.Web.HttpContext.Current.Items["ModelDataContext"];
             }
       }

In this case, each instance of DataContext will automatically be disposed for each hit on the site – and DataContexts will never be shared between two users accessing the site simultaneously. You could also use collections like ViewState, Session, Cache, etc (and I’ve tried several of these). For my purposes, the HttpContext.Items collection scopes my objects for exactly where I want them to be accessible and exactly how long I want them to be alive.

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));
}