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

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.

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]