In my previous post I talked about what is not supported in LINQ to Entities and made a simple comparison with LINQ to SQL show that these unsupported things works fine in LINQ to SQL.
Here I am going to provide workarounds these things. First one will be wrapping the entities with Business Classes. And the second way will be using client evaluation which is converting to work with LINQ to Object. Before you go further, I recommend that you read my previous post to save your time.
Using Business objects as Wrappers for Entity objects
I decided to make my model/business classes as wrappers to entity ones, and then check if this would work with LINQ to Entities or not. I found this would simplify my life a bit. And it allows me also to use my filter extension methods. But of course I'm sure there would be a better way. So feel free to share it and tell me your ideas.
To save some time, the code bellow shows my idea.
public class CategoryWrapper
{
internal Data.Entities.Category categoryEntity;
private IList<ProductWrapper> productsList;
public CategoryWrapper()
{
this.categoryEntity = new Core.Data.Entities.Category();
this.productsList = null;
}
internal CategoryWrapper(Data.Entities.Category categoryEntity)
{
this.categoryEntity = categoryEntity;
this.productsList = null;
}
public int ID
{
get { return this.categoryEntity.CategoryID; }
set { this.categoryEntity.CategoryID = value;}
}
public string Name
{
get { return this.categoryEntity.CategoryName; }
set { this.categoryEntity.CategoryName = value; }
}
//Lazy Loading of products
public IList<ProductWrapper> Products
{
get
{
if (this.productsList == null && this.categoryEntity != null)
{
this.productsList = new List<ProductWrapper>(this.categoryEntity.Products.Count);
if (!this.categoryEntity.Products.IsLoaded)
this.categoryEntity.Products.Load();
foreach (Data.Entities.Product prod in this.categoryEntity.Products)
{
this.productsList.Add(new ProductWrapper(prod));
}
}
else
{
this.productsList = new List<ProductWrapper>();
}
return this.productsList;
}
set
{
this.productsList = value;
}
}
}Few things I want to make note about in the above code:
- Access modifier of the member variable categoryEntity. It is internal because I don't want to expose it to public and at the same time it will be used by Repository classes resides in the same assembly.
- The parametrized internal constructor. This is also used internally and you'll see it is usage when I want to access Category object from within one of its children products.
- I do not add reference to Product Entity here! no need as I can access products from within the categoryEntity itself using Products property.
- Products list property, this property returns list of related products to current category. Note that it performs lazy loading manually. I had to write this logic. However EF team made an open source project Transparent Lazy Loading for Entity Framework. You Can use it to save you some coding and logic time. I'm going to talk about in some post later.
Now how this is going to be used with LINQ to Entities? Very simple; really, here is a code snippet from the repository class that performs the query:
/// <summary>
/// Linq To Entities implementation for Categories
/// </summary>
public IQueryable<Model.CategoryWrapper> GetCategories6()
{
var qry = from c in this.nwEntitiesContext.CategorySet
select new Model.CategoryWrapper
{
categoryEntity = c
};
return qry;
}I really wished if I could use the parametrized constructor instead of using object initializer. But the below is not supported as specified in my previous post.
/// <summary>
/// Linq To Entities implementation for Categories
/// </summary>
public IQueryable<Model.CategoryWrapper> GetCategories6()
{
var qry = from c in this.nwEntitiesContext.CategorySet
select new Model.CategoryWrapper(c);
return qry;
}In the service class I was able to perform filtering using my filter extension methods just as the following:
/// <summary>
/// Get the single category using filter extension method
/// </summary>
/// <returns>single category (CategoryWrapper)</returns>
public CategoryWrapper GetCategory2(int categoryId)
{
return this.repository.GetCategories6().WithCategoryID(categoryId);
} is an extension method to
IQuerable<CategoryWrapper>. Here is how it looks like:
/// <summary>
/// Filters the categories query by CategoryID
/// </summary>
/// <param name="categoryID">The Category Id to filter by</param>
/// <returns>Instance of Category</returns>
public static CategoryWrapper WithCategoryID(
this IQueryable<CategoryWrapper> qry,
int categoryId)
{
return qry.First(p => p.categoryEntity.CategoryID == categoryId);
}Note that I had to filter using
categoryEntity.CategoryID and not by p.ID. If you tried it you'll get another NotSupportedException telling you the following "
The specified type member 'ID' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported." So you can add this to not supported list.
So after filtering the generated SQL would be something like this depending on your filter parameter:
exec sp_executesql N'SELECT
[Limit1].[C1] AS [C1],
[Limit1].[CategoryID] AS [CategoryID],
[Limit1].[CategoryName] AS [CategoryName],
[Limit1].[Description] AS [Description],
[Limit1].[Picture] AS [Picture]
FROM ( SELECT TOP (1)
[Extent1].[CategoryID] AS [CategoryID],
[Extent1].[CategoryName] AS [CategoryName],
[Extent1].[Description] AS [Description],
[Extent1].[Picture] AS [Picture],
1 AS [C1]
FROM [dbo].[Categories] AS [Extent1]
WHERE [Extent1].[CategoryID] = @p__linq__1
) AS [Limit1]',N'@p__linq__1 int',@p__linq__1=1
As you noticed earlier,
CategoryWrapper business class has a property that returns list of products performing lazy loading. So if I tried to iterate through this list at any time, the generated SQL in addition to the above generated one will be:
exec sp_executesql N'SELECT
1 AS [C1],
[Extent1].[ProductID] AS [ProductID],
[Extent1].[ProductName] AS [ProductName],
[Extent1].[SupplierID] AS [SupplierID],
[Extent1].[QuantityPerUnit] AS [QuantityPerUnit],
[Extent1].[UnitPrice] AS [UnitPrice],
[Extent1].[UnitsInStock] AS [UnitsInStock],
[Extent1].[UnitsOnOrder] AS [UnitsOnOrder],
[Extent1].[ReorderLevel] AS [ReorderLevel],
[Extent1].[Discontinued] AS [Discontinued],
[Extent1].[CategoryID] AS [CategoryID]
FROM [dbo].[Products] AS [Extent1]
WHERE ([Extent1].[CategoryID] IS NOT NULL) AND ([Extent1].[CategoryID] = @EntityKeyValue1)',N'@EntityKeyValue1 int',@EntityKeyValue1=1
You can come up with better ideas than this one. But it is so far stratifies my needs. Please if you have any optimization ideas or thoughts about this please share it.
Converting to client evaluation, LINQ to Objects way
As I mentioned in pervious post, Diego Vega, a Program Manager at Entity Framework Team suggested a solution to me 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.
He also provided me with some code snippets which I am going to present below to show his idea. Below is sample after I modified it to match my needs:
public IQueryable<Model.Category> GetCategories7()
{
var qry = from c in nwEntitiesContext.CategorySet.AsEnumerable()
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.AsQueryable();
}As you can see, now I am using
AsEnumerable() to convert to client evaluation. But this will cause one small issue which is All Categories will be retrieved from the Database. However products will be filtered properly.
Here the method that invokes the above method:
public Category GetCategory3(int categoryId)
{
return this.repository.GetCategories7().WithCategoryID(categoryId);
}Note that here I am able to apply filter again. However as I mentioned the filter will be based on client evaluation. Which means that all categories will be retrieved and LINQ will apply the filter in memory.
The generated SQL would be:
SELECT
[Extent1].[CategoryID] AS [CategoryID],
[Extent1].[CategoryName] AS [CategoryName],
[Extent1].[Description] AS [Description],
[Extent1].[Picture] AS [Picture]
FROM [dbo].[Categories] AS [Extent1]
And if you tried to load the products and additional statement will be executed on demand -I'm applying lazy loading- and here is how it will look like:
exec sp_executesql N'SELECT
1 AS [C1],
[Extent1].[CategoryID] AS [CategoryID],
[Extent1].[ProductID] AS [ProductID],
[Extent1].[ProductName] AS [ProductName]
FROM [dbo].[Products] AS [Extent1]
WHERE [Extent1].[ProductID] = @p__linq__1',N'@p__linq__1 int',@p__linq__1=1
So, there is a solution or workaround for any issue. Might not be the best, but soon you'll find or discover a better way. Myself I prefer for the moment to use my business classes as wrapper for Entity ones. But also I consider converting to client evaluation another easy and good working solution for many cases.
Feel free to provide me with your feedback and your thoughts. I need every idea that would help.
You can download the test project to check the full code and evaluate the generated SQL. This project is a modified one of the previous one I made for my first post in this subject.
