Query results with many to many and some parameters using Entity Framework in a more efficiënt way

Question

I've created a working query in Entity Framework and I'm curious if there's a better way to do this. Since there's a lot of field on my models, I will only publish the relevant ones here.

My Models are like this:

public class Category
{
 public int Id { get; set; }
 private ICollection<Product> products = new List<Product>();
 public virtual ICollection<Product> Products { get { return products; } set { products = value; } }
 private ICollection<CategoryLanguage> languages = new List<CategoryLanguage>();
 public virtual ICollection<CategoryLanguage> Languages { get { return languages; } set { languages = value; } }
}
public class CategoryLanguage
{
 public virtual int Id { get; set; }
 public virtual Language Language { get; set; }
 public virtual string Slug { get; set; }
 public virtual string Title { get; set; }
 public virtual string Description { get; set; }
 public virtual string ShortDescription { get; set; }
}
public class Language
{
 public virtual int Id { get; set; }
 public virtual string Iso { get; set; }
}
public class Product
{
 public int Id { get; set; }
 private ICollection<ProductLanguage> languages = new List<ProductLanguage>();
 public virtual ICollection<ProductLanguage> Languages { get { return languages; } set { languages = value; } }
}
public class ProductLanguage
{
 public virtual int Id { get; set; }
 public virtual Language Language { get; set; }
 public virtual string Title { get; set; }
 public virtual string Description { get; set; }
 public virtual string ShortDescription { get; set; }
 public virtual string Slug { get; set; }
}

My Result models looks like this:

public class ShowProductsModel
{
 public string Category { get; set; }
 public IEnumerable<ShowProductsProductModel> Products { get; set; }
}
public class ShowProductsProductModel
{
 public int Id { get; set; }
 public string Title { get; set; }
 public string ShortDescription { get; set; }
}

My Entity Framework query looks like this:

public override async Task<ShowProductsModel> Execute()
{
 string culture = "someCulture";
 string categoryName = "someCategoryName";
 var categoryAndProducts = RebaContext.Categories
 .Where(c => c.Languages.Any(l => l.Slug == categoryName && l.Language.Iso == culture))
 .Select(cat => new ShowProductsModel
 {
 Category = cat.Languages.FirstOrDefault(cl => cl.Language.Iso == culture && cl.Slug == categoryName).Title,
 Products = cat.Products
 .Where(p => p.Categories.Any(c => c.Languages.Any(l => l.Slug == categoryName && l.Language.Iso == culture)))
 .Select(p => new ShowProductsProductModel
 {
 Id = p.Id,
 ShortDescription = p.Languages.FirstOrDefault(l => l.Language.Iso == culture).ShortDescription,
 Title = p.Languages.FirstOrDefault(l => l.Language.Iso == culture).Title
 })
 });
 return await categoryAndProducts.FirstOrDefaultAsync();
}

This Entity Framework query results in the following Transact-SQL:

SELECT 
[Project11].[Id] AS [Id], 
[Project11].[Id1] AS [Id1], 
[Project11].[Id2] AS [Id2], 
[Project11].[Title] AS [Title], 
[Project11].[C1] AS [C1], 
[Project11].[Product] AS [Product], 
[Project11].[ShortDescription] AS [ShortDescription], 
[Project11].[Title1] AS [Title1]
FROM ( SELECT 
 [Limit1].[Id] AS [Id], 
 [Limit1].[Id1] AS [Id1], 
 [Limit1].[Title] AS [Title], 
 [Limit1].[Id2] AS [Id2], 
 [Apply3].[Product] AS [Product], 
 [Apply3].[ShortDescription] AS [ShortDescription], 
 [Apply3].[Title] AS [Title1], 
 CASE WHEN ([Apply3].[Product] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
 FROM (SELECT TOP (1) 
 @p__linq__4 AS [p__linq__4], 
 @p__linq__5 AS [p__linq__5], 
 @p__linq__6 AS [p__linq__6], 
 @p__linq__7 AS [p__linq__7], 
 [Filter2].[Id] AS [Id], 
 [Element1].[Id] AS [Id1], 
 [Element1].[Title] AS [Title], 
 [Element1].[Id1] AS [Id2]
 FROM (SELECT [Extent1].[Id] AS [Id]
 FROM [dbo].[Category] AS [Extent1]
 WHERE EXISTS (SELECT 
 1 AS [C1]
 FROM [dbo].[CategoryLanguage] AS [Extent2]
 INNER JOIN [dbo].[Language] AS [Extent3] ON [Extent2].[LanguageId] = [Extent3].[Id]
 WHERE ([Extent1].[Id] = [Extent2].[CategoryId]) AND (([Extent2].[Slug] = @p__linq__0) OR (([Extent2].[Slug] IS NULL) AND (@p__linq__0 IS NULL))) AND (([Extent3].[Iso] = @p__linq__1) OR (1 = 0))
 ) ) AS [Filter2]
 OUTER APPLY (SELECT TOP (1) 
 [Extent4].[Id] AS [Id], 
 [Extent4].[LanguageId] AS [LanguageId], 
 [Extent4].[CategoryId] AS [CategoryId], 
 [Extent4].[Slug] AS [Slug], 
 [Extent4].[Title] AS [Title], 
 [Extent5].[Id] AS [Id1], 
 [Extent5].[Iso] AS [Iso]
 FROM [dbo].[CategoryLanguage] AS [Extent4]
 INNER JOIN [dbo].[Language] AS [Extent5] ON [Extent4].[LanguageId] = [Extent5].[Id]
 WHERE ([Filter2].[Id] = [Extent4].[CategoryId]) AND (([Extent5].[Iso] = @p__linq__2) OR (1 = 0)) AND (([Extent4].[Slug] = @p__linq__3) OR (([Extent4].[Slug] IS NULL) AND (@p__linq__3 IS NULL))) ) AS [Element1] ) AS [Limit1]
 OUTER APPLY (SELECT [Project9].[Product] AS [Product], [Project9].[ShortDescription] AS [ShortDescription], [Element3].[Title] AS [Title]
 FROM (SELECT 
 [Filter8].[Product] AS [Product], 
 [Element2].[ShortDescription] AS [ShortDescription]
 FROM (SELECT [Project4].[Product] AS [Product]
 FROM ( SELECT 
 [Extent6].[Product] AS [Product]
 FROM [dbo].[ProductCategory] AS [Extent6]
 WHERE [Limit1].[Id] = [Extent6].[Category]
 ) AS [Project4]
 WHERE EXISTS (SELECT 
 1 AS [C1]
 FROM ( SELECT 
 [Extent7].[Category] AS [Category]
 FROM [dbo].[ProductCategory] AS [Extent7]
 WHERE [Project4].[Product] = [Extent7].[Product]
 ) AS [Project5]
 WHERE EXISTS (SELECT 
 1 AS [C1]
 FROM [dbo].[CategoryLanguage] AS [Extent8]
 INNER JOIN [dbo].[Language] AS [Extent9] ON [Extent8].[LanguageId] = [Extent9].[Id]
 WHERE ([Project5].[Category] = [Extent8].[CategoryId]) AND (([Extent8].[Slug] = @p__linq__4) OR (([Extent8].[Slug] IS NULL) AND (@p__linq__4 IS NULL))) AND (([Extent9].[Iso] = @p__linq__5) OR (1 = 0))
 )
 ) ) AS [Filter8]
 OUTER APPLY (SELECT TOP (1) 
 [Extent10].[LanguageId] AS [LanguageId], 
 [Extent10].[ProductId] AS [ProductId], 
 [Extent10].[ShortDescription] AS [ShortDescription], 
 [Extent11].[Id] AS [Id], 
 [Extent11].[Iso] AS [Iso]
 FROM [dbo].[ProductLanguage] AS [Extent10]
 INNER JOIN [dbo].[Language] AS [Extent11] ON [Extent10].[LanguageId] = [Extent11].[Id]
 WHERE ([Filter8].[Product] = [Extent10].[ProductId]) AND (([Extent11].[Iso] = @p__linq__6) OR (1 = 0)) ) AS [Element2] ) AS [Project9]
 OUTER APPLY (SELECT TOP (1) 
 [Extent12].[LanguageId] AS [LanguageId], 
 [Extent12].[ProductId] AS [ProductId], 
 [Extent12].[Title] AS [Title], 
 [Extent13].[Id] AS [Id], 
 [Extent13].[Iso] AS [Iso]
 FROM [dbo].[ProductLanguage] AS [Extent12]
 INNER JOIN [dbo].[Language] AS [Extent13] ON [Extent12].[LanguageId] = [Extent13].[Id]
 WHERE ([Project9].[Product] = [Extent12].[ProductId]) AND (([Extent13].[Iso] = @p__linq__7) OR (1 = 0)) ) AS [Element3] ) AS [Apply3]
) AS [Project11]
ORDER BY [Project11].[Id] ASC, [Project11].[Id1] ASC, [Project11].[Id2] ASC, [Project11].[C1] ASC',N'@p__linq__0 nvarchar(4000),@p__linq__1 nvarchar(4000),@p__linq__2 nvarchar(4000),@p__linq__3 nvarchar(4000),@p__linq__4 nvarchar(4000),@p__linq__5 nvarchar(4000),@p__linq__6 nvarchar(4000),@p__linq__7 nvarchar(4000)',@p__linq__0=N'Gitaar',@p__linq__1=N'nl',@p__linq__2=N'nl',@p__linq__3=N'Gitaar',@p__linq__4=N'Gitaar',@p__linq__5=N'nl',@p__linq__6=N'nl',@p__linq__7=N'nl'

I feel like there should be a more efficient way to query what I want. I've created a query that fulfills my needs. It fetches one category name and includes all underlying products. The category should have a specified slug (param: categoryName) and certain language (param: culture). all fetched products should also have a certain language (param: culture). Is there any way I could get Entity Framework to query something like that?

declare @culture nvarchar(max), @categoryName nvarchar(max)
SET @culture = 'someCulture'
SET @categoryName = 'someCategoryName'
select c.id, p.id, pl.shortdescription, pl.title
from Category c
inner join CategoryLanguage cl on c.id = cl.CategoryId
inner join language l1 on cl.LanguageId = l1.id
inner join ProductCategory pc on c.id = pc.Category
inner join product p on pc.Product = p.id
inner join productlanguage pl on p.id = pl.ProductId
inner join Language l2 on pl.LanguageId = l2.id
where l1.Iso = @culture
and cl.Slug = @categoryName
and l2.Iso = @culture

• Jeroen Vannevel: Could you specify what your query does exactly?
• annemartijn: It fetches one category name and includes all underlying products. the category should have a specified slug (param: categoryName) and certain language (param: culture). all fetched products should also have a certain language (param: culture).
• Mathieu Guindon: I have rolled back the last edit. Please see what you may and may not do after receiving answers .

Answer 1

private ICollection<Product> products = new List<Product>();
public virtual ICollection<Product> Products { get { return products; } set { products = value; } }

You don't have to get rid of your automatically-implemented properties to make sure your collections are initialized. Instead, use the default constructor to do this.

public Category()
{
 Products = new List<Product>();
}
public virtual ICollection<Product> Products { get; set; }

---

public override async Task<ShowProductsModel> Execute()

According to convention, asynchronous methods should have the "Async" postfix to indicate this.

This turns into

public override async Task<ShowProductsModel> ExecuteAsync()

---

You indicate that only one category should be queried. This contrasts your declaration of

.Where(c => c.Languages.Any(l => l.Slug == categoryName && l.Language.Iso == culture))

Instead I would use SingleOrDefault():

.SingleOrDefault(c => c.Languages.Any(l => l.Slug == categoryName && l.Language.Iso == culture))

---

Products = cat.Products
 .Where(p => p.Categories.Any(c => 
 c.Languages.Any(l => 
 l.Slug == categoryName && l.Language.Iso == culture)))

This query was already executed before. In fact, this is the query you started with except now you suddenly start from the viewpoint of Products instead of Categories. You still have access to cat in this context which is the category that you're working with.

What you do here is loop over all those products in that category, look at all their categories and then take the first one that has your specified information, essentially doing work that isn't needed.

I can't execute it to verify of course but I believe you can simply omit this entire Where clause unless I'm missing something.

---

There is a distinction between FirstAndDefault() and SingleAndDefault() that is relevant to other programmers reading your code: the former says there can be more than one, but take one and return default otherwise. The latter says there can only be one so take that one and return default otherwise.

I believe you can change all of these to SingleAndDefault() aside from your very last one in the return statement.

---

Which brings me to this: by using SingleOrDefault() at the start you no longer have a collection on which you perform Select() but a single object. This makes sure you can drop the remaining FirstOrDefaultAsync() since the Select() will already return a single object.

• annemartijn: I agree, the second Where can be omitted. However changing the first Where into FirstOrDefault will result in two seperate queries hitting the database. Category would be queried first and I will have to hit the database for a second time to fetch all underlying products. Since Entity Framework doesn't support Future queries and I can get what I want using a normal (not so special) query, why would I want to do two queries?
• Jeroen Vannevel: I'm not an EF expert so this might be out of my league but as far as I know, FirstOrDefault() and SingleOrDefault() simply translate to a TOP 1 sql statement. Why would this suddenly force your query to be executed in two times? Have you confirmed two queries are actually being sent?
• annemartijn: Does this gist sort of do what you suggested, because that would result in two queries send to database.
• Jeroen Vannevel: Ahh, of course you can't. In that case you could stick with the Where indeed. The cost of making two separate queries is probably higher than iterating over multiple irrelevant categories.
• Aron: @JeroenVannevel No. .SingleOrDefault() results in a TOP 2. You need to make sure that there is no second row. However it sounds like @annemartijn is refering to the fact that LinqToEntities does not support .SingleOrDefault except as the last operator in a query (you can't nest it inside).

Answer 2

I've created a LINQ Entity Framework query that fulfills my needs and generates a cleaner Transact-SQL query. My final solution depends on the answer given by Jeroen Vannevel, so his answer is marked as "accepted".

public override async Task<ShowProductsModel> Execute()
{
 var categoryAndProducts =
 from cl in RebaContext.CategoryLanguages
 where cl.Language.Iso == culture
 where cl.Slug == categoryName
 where cl.Enabled
 select new ShowProductsModel
 {
 Category = cl.Title,
 Products = from product in cl.Category.Products
 where product.Published
 from pl in product.Languages
 where pl.Enabled
 where pl.Language.Iso == culture
 select new ShowProductsProductModel
 {
 Id = pl.ProductId,
 ShortDescription = pl.ShortDescription,
 Title = pl.Title,
 Slug = pl.Slug,
 Price = pl.Product.Price
 }
 };
 return await categoryAndProducts.FirstOrDefaultAsync();
}

This LINQ Entity Framework query generates the following Transact-SQL:

 SELECT TOP (2) 
 [Filter1].[Id1] AS [Id], 
 [Filter1].[Type] AS [Type], 
 [Filter1].[FileContent] AS [FileContent], 
 [Element1].[Slug] AS [Slug]
 FROM (SELECT [Extent1].[Id] AS [Id1], [Extent2].[Type] AS [Type], [Extent2].[FileContent] AS [FileContent]
 FROM [dbo].[Product] AS [Extent1]
 LEFT OUTER JOIN [dbo].[ProductImage] AS [Extent2] ON [Extent1].[CoverImageId] = [Extent2].[Id]
 WHERE [Extent1].[Id] = @p__linq__0 ) AS [Filter1]
 OUTER APPLY (SELECT TOP (1) 
 [Extent3].[LanguageId] AS [LanguageId], 
 [Extent3].[ProductId] AS [ProductId], 
 [Extent3].[Slug] AS [Slug], 
 [Extent4].[Id] AS [Id], 
 [Extent4].[Iso] AS [Iso]
 FROM [dbo].[ProductLanguage] AS [Extent3]
 INNER JOIN [dbo].[Language] AS [Extent4] ON [Extent3].[LanguageId] = [Extent4].[Id]
 WHERE ([Filter1].[Id1] = [Extent3].[ProductId]) AND (([Extent4].[Iso] = @p__linq__1) OR (1 = 0)) ) AS [Element1]