Recently and after .Net 3.5 SP1 & Visual Studio.Net 2008 SP1 released, I started to play with LINQ to Entities. Really Entity Framework is interesting. Maybe not the best ORM tool, but still it is interesting and easy to use plus it is all one IDE at the end.

At the same time I was watching screen-casts made by Rob Conery about Building MVC Storefront along with TDD. In fact he was using LINQ to SQL. And I liked his coding techniques presented in his sample application and screen-casts.

I decided to play around with LINQ to Entities with MVC. Here I am going to talk about my adventures with LINQ to Entities, and really it is completely different from working with LINQ to SQL.

My recommendation for every developer is to start by reading Supported and Unsupported Methods (LINQ to Entities). Yes it will save you sometime. Do You know LINQ to SQL? Then you are familiar with LINQ. But you need to know what there and what is not there in LINQ to Entities. This even Might help you to build a wish list for LINQ to Entities also ;O).

Here I'm going to use some code samples presented in MVC Storefront and show some of the techniques that will not work with LINQ to Entities. And I will compare code with LINQ to SQL. In some other post, I'm going to present some other workarounds provided by ADO.NET Team when I asked them about the issues I have with LINQ to Entities. Also I'll present my own workarounds. And I'll compare the Generate SQL statements. I am not query optimizer, so I will not say what is the best choice. But you can help me in that.

Project Structure

When I started I decided not to use the generated entities as Business Objects. I wanted to keep them as Data Objects and not expose them to the Presentation. So I had to make another set of classes that should work as my Business objects. For demonstration I built a simple project structure as shown below:

You can download this project. Here is an explanation of this project structure to be more clarified:

• Core.Data.Entities & Core.Data.LinqToSql (Data Classes) namespaces
  Namespaces that contain Entity Classes generated by Entity Framework and LINQ to SQL Classes generated by LINQ to SQL generated.
• Core.Data.Repository namespace
  Contain Data Services classes that is responsible for querying the Data Classes using LINQ.
• Core.Model namespace
  Business Classes, very simple.
• Core.Business.Services namespace
  Business services classes that is supposed to be directly used by presentation. I'm using them in Test Project as well. They interact with classes in Core.Data.Repository namespace, but really should know anything about Core.Data.* namespaces.
• Core.Model.Filters namespace
  Extension Methods classes to perform LINQ filters over returned results. This is to simplify queries but applying filter over generic queries.

LINQ to SQL, where everything is possible:

Simply I'm using 2 tables from Northwind database, Categories and Products. Lets see how to query those 2 tables using LINQ to SQL with the structure show above. I have a Class under Core.Data.Repository called CatalogRepositoryLinqToSql. It worth to mention that I made all classes in my generated LINQ to SQL as internal. Here is the code:

internal partial class CatalogRepositoryLinqToSql : IDisposable
{
    NorthwindDataContext nwDataContext;
    
    private CatalogRepositoryLinqToSql(NorthwindDataContext dataContext) 
    {
        nwDataContext = dataContext;    
    }
    public static CatalogRepositoryLinqToSql CreateRepository()
    {
        return new CatalogRepositoryLinqToSql(new NorthwindDataContext());
    }
    /// <summary>
    /// Linq To Sql implementation for Categories
    /// </summary>
    /// <returns>IQueryable of Categories</returns>
    public IQueryable<Model.Category> GetCategories()
    {
        var qry = from c in nwDataContext.Categories
                  let products = this.GetProducts().WithCategoryID(c.CategoryID)
                  select new Model.Category
                  {
                      ID = c.CategoryID,
                      Name = c.CategoryName,
                      Products = new Model.LazyList<Core.Model.Product>(products)
                  };
        return qry;            
    }        
    /// <summary>
    /// Linq To Sql Implementation for Products
    /// </summary>
    /// <returns></returns>
    public IQueryable<Model.Product> GetProducts()
    {
        var qry = from p in nwDataContext.Products
                  select new Model.Product
                  {
                      CategoryID = p.CategoryID,
                      ID = p.ProductID,
                      Name = p.ProductName
                  };
        return qry;
    }
    public void Dispose()
    {
        if (nwDataContext != null)
        {
            nwDataContext.Dispose();
        }
    }
}

I don't want to go through explanation of the code. You can watch early screen casts of MVC Storefront to have better understanding of the above code. But here are few hints:

• GetProducts method it returns a LINQ query (IQueryable<Mode.Product>). It builds the query then I can use my Filter Extension Method WithCategoryID for (IQueryable<Mode.Product>), to get Products of certain category. The reason why I do not hit Category.Products in LINQ to SQL generated code is because it will hit the Database, and I wanted to apply lazy loading.
• Model.LazyList is custom class to apply lazy loading for Business Class.

When the above methods (in code) are invoked, everything move is just fine. And the database is hit exactly when needed. The Repository Classes are consumed by the Business Services Classes. So for the above class I have a service in Core.Business.Services called CatalogServiceForLinqToSql which invokes the the LINQ to SQL repository class as the following:

public class CatalogServiceForLinqToSql : IDisposable
{
    private CatalogRepositoryLinqToSql repository;

    public CatalogServiceForLinqToSql()
    {
        repository = CatalogRepositoryLinqToSql.CreateRepository();
    }
    /// <summary>
    /// Get the categories from the repository
    /// </summary>
    /// <returns>List of categories</returns>
    public IList<Category> GetCategories()
    {
        return this.repository.GetCategories().ToList();
    }
    /// <summary>
    /// Returns a single category by ID
    /// </summary>
    /// <param name="id">The Category ID</param>
    /// <returns>Category</returns>
    /// <remarks>
    /// Possible generated SQL:
    /// "exec sp_executesql N'SELECT [t0].[CategoryID] AS [ID], ....
    /// FROM [dbo].[Categories] AS [t0]
    /// WHERE [t0].[CategoryID] = @p0',N'@p0 int',@p0=1"
    /// 
    /// If Products property of Category class is called Lazy loading
    /// will take place
    /// Additional generate SQL:
    /// "exec sp_executesql N'SELECT [t0].[CategoryID],....
    /// FROM [dbo].[Products] AS [t0]
    /// WHERE [t0].[CategoryID] = @p0',N'@p0 int',@p0=1"
    /// </remarks>
    public Category GetCategory(int categoryId)
    {
        return this.repository.GetCategories().WithCategoryID(categoryId);
    }
    #region IDisposable Members
    public void Dispose()
    {
        if (this.repository != null)
        {
            this.repository.Dispose();
        }
    }
    #endregion
}

Now if you tried to return products of a certain category, you just need to access Products property of Core.Model.Category Class. Note that this is lazy loading, which means products are not initially loaded when you retrieved the category. It is just loaded when you want to access the products.

LINQ to Entities, not everything considered common sense!

Well, at first time I thought that if I just reused the code above but instead of using LINQ to SQL I would use LINQ to Entities I would get the same results and I am Ok! well done! But the answer is NO! this will not work. Now I am going to explore different things that are not Supported by LINQ to Entities, but in fact they are supported by LINQ to SQL.

To redo the work with EF, I created Entities classes for the same tables from northwind (Categories and Products).
Then created another Repository Class Core.Data.Repository.CatalogRepositoryLinqToEntities that works exactly the way the LINQ to SQL repository class work. Also a Business Service Class is created CatalogServiceForLinqToEntities.

Custom Methods & Extension Methods cannot be translated into a store expression

What is that?! well ok have a look at the following code snippet from CatalogRepositoryLinqToEntities class:

/// <summary>
/// Linq To Entities implementation for Categories
/// </summary>
/// <returns>IQueryable of Categories</returns>
/// <remarks>
/// Will throw System.NotSupportedException: 
/// LINQ to Entities does not recognize the method 
/// 'System.Linq.IQueryable`1[Core.Model.Product] WithCategoryID(System.Linq.IQueryable`1[Core.Model.Product], Int32)' method, 
/// and this method cannot be translated into a store expression
/// 
/// Explanation:
/// LINQ to Entities didn't recognize the my custom filter 
/// Extension Method IQueryable(of T).WithCategoryID(int)
/// </remarks>
public IQueryable<Model.Category> GetCategories1()
{
    var qry = from c in nwEntitiesContext.CategorySet
              let products = this.GetProducts().WithCategoryID(c.CategoryID)
              select new Model.Category
              {
                  ID = c.CategoryID,
                  Name = c.CategoryName,
                  Products = new Model.LazyList<Core.Model.Product>(products)
              };
    return qry;
}

I commented the code for better explanation. On line 18, I'm making a calls to GetProducts method then trying to apply filter using WithCategoryID Extension Method. Exactly the same way as I did in LINQ to SQL. And I got an exception NotSupportedException. And the explanation is mentioned in the code comment.

So I made a work around, thinking myself smart enough to get away from this exception. I changed line 18 with the following:

let products = this.GetProducts().Where(p => p.CategoryID == c.CategoryID)

When returned to ADO.NET team regarding this issue and in fact they responsed in no time. Diego Vega, a Program Manager at Entity Framework Team sent me the following explanation of the above issue:

"By design, LINQ to Entities requires the whole LINQ query expression to be translated to a server query. Only a few uncorrelated subexpressions (expressions in the query that do not depend on the results from the server) are evaluated on the client before the query is translated. Arbitrary method invocations that do not have a known translation, like GetProducts() in this case, are not supported."

LINQ to Entities only support Parameterless constructors and Initializers

So lets continue with the rest of issues I faced. Diego also suggested to make a sub query for products instead of directly invoking a method from within a a LINQ query. So I can do something like this:

var productsQuery = this.GetProducts();
var qry = from c in nwEntitiesContext.CategorySet
	let products = productsQuery.Where(p => p.CategoryID == c.CategoryID)
	.....

/// <summary>
/// Linq To Entities implementation for Categories
/// </summary>
/// <returns>IQueryable of Categories</returns>
/// <remarks>
/// Will throw System.NotSupportedException: 
/// Only parameterless constructors and initializers are supported in 
/// LINQ to Entities.
/// 
/// Explanation:
/// Because I am trying to initialize LazyList with new LazyList(IQuerable)
/// </remarks>
public IQueryable<Model.Category> GetCategories3()
{
    var productsQuery = this.GetProducts();
    var qry = from c in nwEntitiesContext.CategorySet
            let products = productsQuery.Where(p=>p.CategoryID==c.CategoryID)
            select new Model.Category
            {
                ID = c.CategoryID,
                Name = c.CategoryName,
                Products = new Model.LazyList<Core.Model.Product>(products)
            };
    return qry;
}

IEnumrable types cannot be initialized in LINQ to Entities query

Another attempt to be smart, I decided to work around the issue above by using LazyList object initializer but that just didn't work out. Check it out below:

/// <summary>
/// Linq To Entities implementation for Categories
/// Workaround for GetCategories3
/// </summary>
/// <returns>IQueryable of Categories</returns>
/// <remarks>
/// Will throw System.NotSupportedException: 
/// A type that implements IEnumerable 'Core.Model.LazyList`1' 
/// cannot be initialized in a LINQ to Entities query.
/// 
/// Explanation:
/// Cannot make new instance of anything that implements IEnumerable
/// </remarks>
public IQueryable<Model.Category> GetCategories4()
{
    var productsQuery = this.GetProducts();
    var qry = from c in nwEntitiesContext.CategorySet
           let products = productsQuery.Where(p=>p.CategoryID==c.CategoryID)
           select new Model.Category
           {
               ID = c.CategoryID,
               Name = c.CategoryName,
               Products = new Model.LazyList<Core.Model.Product>
               {
                   query = products
               }
           };
    return qry;
}

Well Diego also suggested me a solution which is to switch to client evaluation of LINQ queries (LINQ to Objects). This will require to invoke AsEnumrable method for my LINQ to Entities queries. He also mentioned that in LINQ to SQL, the switch to client side evaluation is implicit and always happens in the outermost projection.

Check out my next post where I explored this solution as well as providing my own one. For now you can download the attached project which include a test project to be able to observe what I presented here.

Conclusion

Entity Framework is really interesting. But I can say it is the first version, still a baby, but I believe that this baby will grow up one day and become a good man ;o) that can take over great responsibilities.