4
\$\begingroup\$

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
\$\endgroup\$
  • \$\begingroup\$ Could you specify what your query does exactly? \$\endgroup\$ – Jeroen Vannevel Jan 17 '15 at 14:21
  • \$\begingroup\$ 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). \$\endgroup\$ – annemartijn Jan 17 '15 at 14:25
  • \$\begingroup\$ I have rolled back the last edit. Please see what you may and may not do after receiving answers. \$\endgroup\$ – Mathieu Guindon Jan 17 '15 at 22:36
5
\$\begingroup\$
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.

\$\endgroup\$
  • \$\begingroup\$ 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? \$\endgroup\$ – annemartijn Jan 17 '15 at 14:55
  • \$\begingroup\$ 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? \$\endgroup\$ – Jeroen Vannevel Jan 17 '15 at 15:03
  • \$\begingroup\$ Does this gist sort of do what you suggested, because that would result in two queries send to database. \$\endgroup\$ – annemartijn Jan 17 '15 at 15:22
  • 1
    \$\begingroup\$ 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. \$\endgroup\$ – Jeroen Vannevel Jan 17 '15 at 15:32
  • 1
    \$\begingroup\$ @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). \$\endgroup\$ – Aron Jan 17 '15 at 18:42
1
\$\begingroup\$

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]
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.