replaced http://codereview.stackexchange.com/ with https://codereview.stackexchange.com/
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 Jeroen Vannevel, so his answer is marked as "accepted".
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".
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".
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
let productlanguages = 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,
}
select new ShowProductsModel
{ Slug = pl.Slug,
Category Price = clpl.Title,Product.Price
Products = productlanguages }
};
return await categoryAndProducts.FirstOrDefaultAsync();
}
This LINQ Entity Framework query generates the following Transact-SQL, that does not include any OUTER APPLY statements:
SELECT [Limit1].[Id] ASTOP [Id],(2)
[Limit1][Filter1].[Title][Id1] AS [Title][Id],
[Limit1][Filter1].[Id1][Type] AS [Id1][Type],
[Limit1][Filter1].[Category][FileContent] AS [Category][FileContent],
[Limit1][Element1].[Product][Slug] AS [Product], [Slug]
[Join3].[LanguageId] AS [LanguageId],
FROM (SELECT [Join3][Extent1].[ProductId][Id] AS [ProductId][Id1], [Join3][Extent2].[Title][Type] AS [Title1][Type],
[Join3][Extent2].[ShortDescription][FileContent] AS [ShortDescription], [FileContent]
CASE WHEN ([Join3].[LanguageId] IS NULL) THEN CAST(NULL AS int) ELSEFROM 1 END[dbo].[Product] AS [C1][Extent1]
FROM (SELECT TOP (1)
LEFT OUTER JOIN [Extent1][dbo].[Id][ProductImage] AS [Id],
[Extent2] ON [Extent1].[Title] AS [Title],
[CoverImageId] = [Extent2].[Id] AS [Id1],
[Extent3].[Category] AS [Category],
WHERE [Extent1].[Id] = @p__linq__0 [Extent3].[Product]) AS [Product][Filter1]
OUTER FROMAPPLY (SELECT [dbo].[CategoryLanguage]TOP AS(1) [Extent1]
INNER JOIN [dbo][Extent3].[Language][LanguageId] AS [Extent2] ON [Extent1].[LanguageId] =, [Extent2].[Id]
INNER JOIN [dbo][Extent3].[ProductCategory][ProductId] AS [Extent3] ON [Extent1].[CategoryId] =[ProductId], [Extent3].[Category]
WHERE (([Extent2].[Iso] = @p__linq__0) OR (1 = 0)) AND (([Extent1][Extent3].[Slug] = @p__linq__1) ORAS (([Extent1].[Slug] IS NULL) AND (@p__linq__1 IS NULL))) ) AS, [Limit1]
LEFT OUTER JOIN (SELECT [Extent4].[LanguageId][Id] AS [LanguageId][Id], [Extent4].[ProductId] AS [ProductId], [Extent4].[Title] AS [Title], [Extent4].[ShortDescription] AS [ShortDescription], [Extent5][Extent4].[Iso] AS [Iso]
FROM [dbo].[ProductLanguage] AS [Extent4][Extent3]
INNER JOIN [dbo].[Language] AS [Extent5][Extent4] ON [Extent4][Extent3].[LanguageId] = [Extent5][Extent4].[Id] ) AS [Join3] ON WHERE ([Limit1][Filter1].[Product][Id1] = [Join3][Extent3].[ProductId]) AND (([Join3][Extent4].[Iso] = @p__linq__2@p__linq__1) OR (1 = 0))
ORDER BY [Id] ASC, [Id1] ASC, [Category] ASC, [Product] ASC,) [C1]AS ASC[Element1]
I've created a LINQ Entity Framework query that fulfills my needs and generates a cleaner Transact-SQL query.
public override async Task<ShowProductsModel> Execute()
{
var categoryAndProducts =
from cl in RebaContext.CategoryLanguages
where cl.Language.Iso == culture && cl.Slug == categoryName
from product in cl.Category.Products
let productlanguages =
from pl in product.Languages
where pl.Language.Iso == culture
select new ShowProductsProductModel
{
Id = pl.ProductId,
ShortDescription = pl.ShortDescription,
Title = pl.Title
}
select new ShowProductsModel
{
Category = cl.Title,
Products = productlanguages
};
return await categoryAndProducts.FirstOrDefaultAsync();
}
This LINQ Entity Framework query generates the following Transact-SQL, that does not include any OUTER APPLY statements:
SELECT [Limit1].[Id] AS [Id],
[Limit1].[Title] AS [Title],
[Limit1].[Id1] AS [Id1],
[Limit1].[Category] AS [Category],
[Limit1].[Product] AS [Product],
[Join3].[LanguageId] AS [LanguageId],
[Join3].[ProductId] AS [ProductId], [Join3].[Title] AS [Title1],
[Join3].[ShortDescription] AS [ShortDescription],
CASE WHEN ([Join3].[LanguageId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
FROM (SELECT TOP (1)
[Extent1].[Id] AS [Id],
[Extent1].[Title] AS [Title],
[Extent2].[Id] AS [Id1],
[Extent3].[Category] AS [Category],
[Extent3].[Product] AS [Product]
FROM [dbo].[CategoryLanguage] AS [Extent1]
INNER JOIN [dbo].[Language] AS [Extent2] ON [Extent1].[LanguageId] = [Extent2].[Id]
INNER JOIN [dbo].[ProductCategory] AS [Extent3] ON [Extent1].[CategoryId] = [Extent3].[Category]
WHERE (([Extent2].[Iso] = @p__linq__0) OR (1 = 0)) AND (([Extent1].[Slug] = @p__linq__1) OR (([Extent1].[Slug] IS NULL) AND (@p__linq__1 IS NULL))) ) AS [Limit1]
LEFT OUTER JOIN (SELECT [Extent4].[LanguageId] AS [LanguageId], [Extent4].[ProductId] AS [ProductId], [Extent4].[Title] AS [Title], [Extent4].[ShortDescription] AS [ShortDescription], [Extent5].[Iso] AS [Iso]
FROM [dbo].[ProductLanguage] AS [Extent4]
INNER JOIN [dbo].[Language] AS [Extent5] ON [Extent4].[LanguageId] = [Extent5].[Id] ) AS [Join3] ON ([Limit1].[Product] = [Join3].[ProductId]) AND (([Join3].[Iso] = @p__linq__2) OR (1 = 0))
ORDER BY [Id] ASC, [Id1] ASC, [Category] ASC, [Product] ASC, [C1] ASC
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]
public override async Task<ShowProductsModel> Execute()
{
var categoryAndProducts =
from cl in RebaContext.CategoryLanguages
where cl.Language.Iso == culture && cl.Slug == categoryName
from product in cl.Category.Products
let productlanguages =
from pl in product.Languages
where pl.Language.Iso == culture
select plnew ShowProductsProductModel
select new ShowProductsModel
{
{
CategoryId = clpl.TitleProductId,
Products = productlanguages.Select(pl => newShortDescription ShowProductsProductModel= pl.ShortDescription,
{
Title = pl.Title
Id = pl.ProductId,}
select new ShowProductsModel
ShortDescription = pl.ShortDescription,{
TitleCategory = plcl.Title,
})Products = productlanguages
};
return await categoryAndProducts.FirstOrDefaultAsync();
}
SELECT
[Limit1].[Id] AS [Id],
[Limit1].[Title] AS [Title],
[Limit1].[Id1] AS [Id1],
[Limit1].[Category] AS [Category],
[Limit1].[Product] AS [Product],
[Join3].[LanguageId] AS [LanguageId],
[Join3].[ProductId] AS [ProductId],
[Join3].[Title] AS [Title1],
[Join3].[ShortDescription] AS [ShortDescription],
CASE WHEN ([Join3].[LanguageId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
FROM (
(SELECT TOP (1)
[Extent1].[Id] AS [Id],
[Extent1].[Title] AS [Title],
[Extent2].[Id] AS [Id1],
[Extent3].[Category] AS [Category],
[Extent3].[Product] AS [Product]
FROM [dbo].[CategoryLanguage] AS [Extent1]
INNER JOIN [dbo].[Language] AS [Extent2] ON [Extent1].[LanguageId] = [Extent2].[Id]
INNER JOIN [dbo].[ProductCategory] AS [Extent3] ON [Extent1].[CategoryId] = [Extent3].[Category]
WHERE (([Extent2].[Iso] = @p__linq__0) OR (1 = 0)) AND (([Extent1].[Slug] = @p__linq__1) OR (([Extent1].[Slug] IS NULL) AND (@p__linq__1 IS NULL))) ) AS [Limit1]
LEFT OUTER JOIN (
(SELECT [Extent4].[LanguageId] AS [LanguageId], [Extent4].[ProductId] AS [ProductId], [Extent4].[Title] AS [Title], [Extent4].[ShortDescription] AS [ShortDescription], [Extent5].[Iso] AS [Iso]
FROM [dbo].[ProductLanguage] AS [Extent4]
INNER JOIN [dbo].[Language] AS [Extent5] ON [Extent4].[LanguageId] = [Extent5].[Id] ) AS [Join3] ON ([Limit1].[Product] = [Join3].[ProductId]) AND (([Join3].[Iso] = @p__linq__2) OR (1 = 0))
ORDER BY [Id] ASC, [Id1] ASC, [Category] ASC, [Product] ASC, [C1] ASC
public override async Task<ShowProductsModel> Execute()
{
var categoryAndProducts =
from cl in RebaContext.CategoryLanguages
where cl.Language.Iso == culture && cl.Slug == categoryName
from product in cl.Category.Products
let productlanguages =
from pl in product.Languages
where pl.Language.Iso == culture
select pl
select new ShowProductsModel
{
Category = cl.Title,
Products = productlanguages.Select(pl => new ShowProductsProductModel
{
Id = pl.ProductId,
ShortDescription = pl.ShortDescription,
Title = pl.Title
})
};
return await categoryAndProducts.FirstOrDefaultAsync();
}
SELECT
[Limit1].[Id] AS [Id],
[Limit1].[Title] AS [Title],
[Limit1].[Id1] AS [Id1],
[Limit1].[Category] AS [Category],
[Limit1].[Product] AS [Product],
[Join3].[ProductId] AS [ProductId],
[Join3].[Title] AS [Title1],
[Join3].[ShortDescription] AS [ShortDescription],
CASE WHEN ([Join3].[LanguageId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
FROM (
SELECT TOP (1)
[Extent1].[Id] AS [Id],
[Extent1].[Title] AS [Title],
[Extent2].[Id] AS [Id1],
[Extent3].[Category] AS [Category],
[Extent3].[Product] AS [Product]
FROM [dbo].[CategoryLanguage] AS [Extent1]
INNER JOIN [dbo].[Language] AS [Extent2] ON [Extent1].[LanguageId] = [Extent2].[Id]
INNER JOIN [dbo].[ProductCategory] AS [Extent3] ON [Extent1].[CategoryId] = [Extent3].[Category]
WHERE (([Extent2].[Iso] = @p__linq__0) OR (1 = 0)) AND (([Extent1].[Slug] = @p__linq__1) OR (([Extent1].[Slug] IS NULL) AND (@p__linq__1 IS NULL))) ) AS [Limit1]
LEFT OUTER JOIN (
SELECT [Extent4].[LanguageId] AS [LanguageId], [Extent4].[ProductId] AS [ProductId], [Extent4].[Title] AS [Title], [Extent4].[ShortDescription] AS [ShortDescription], [Extent5].[Iso] AS [Iso]
FROM [dbo].[ProductLanguage] AS [Extent4]
INNER JOIN [dbo].[Language] AS [Extent5] ON [Extent4].[LanguageId] = [Extent5].[Id] ) AS [Join3] ON ([Limit1].[Product] = [Join3].[ProductId]) AND (([Join3].[Iso] = @p__linq__2) OR (1 = 0))
ORDER BY [Id] ASC, [Id1] ASC, [Category] ASC, [Product] ASC
public override async Task<ShowProductsModel> Execute()
{
var categoryAndProducts =
from cl in RebaContext.CategoryLanguages
where cl.Language.Iso == culture && cl.Slug == categoryName
from product in cl.Category.Products
let productlanguages =
from pl in product.Languages
where pl.Language.Iso == culture
select new ShowProductsProductModel
{
Id = pl.ProductId,
ShortDescription = pl.ShortDescription,
Title = pl.Title
}
select new ShowProductsModel
{
Category = cl.Title,
Products = productlanguages
};
return await categoryAndProducts.FirstOrDefaultAsync();
}
SELECT
[Limit1].[Id] AS [Id],
[Limit1].[Title] AS [Title],
[Limit1].[Id1] AS [Id1],
[Limit1].[Category] AS [Category],
[Limit1].[Product] AS [Product],
[Join3].[LanguageId] AS [LanguageId],
[Join3].[ProductId] AS [ProductId],
[Join3].[Title] AS [Title1],
[Join3].[ShortDescription] AS [ShortDescription],
CASE WHEN ([Join3].[LanguageId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
FROM (SELECT TOP (1)
[Extent1].[Id] AS [Id],
[Extent1].[Title] AS [Title],
[Extent2].[Id] AS [Id1],
[Extent3].[Category] AS [Category],
[Extent3].[Product] AS [Product]
FROM [dbo].[CategoryLanguage] AS [Extent1]
INNER JOIN [dbo].[Language] AS [Extent2] ON [Extent1].[LanguageId] = [Extent2].[Id]
INNER JOIN [dbo].[ProductCategory] AS [Extent3] ON [Extent1].[CategoryId] = [Extent3].[Category]
WHERE (([Extent2].[Iso] = @p__linq__0) OR (1 = 0)) AND (([Extent1].[Slug] = @p__linq__1) OR (([Extent1].[Slug] IS NULL) AND (@p__linq__1 IS NULL))) ) AS [Limit1]
LEFT OUTER JOIN (SELECT [Extent4].[LanguageId] AS [LanguageId], [Extent4].[ProductId] AS [ProductId], [Extent4].[Title] AS [Title], [Extent4].[ShortDescription] AS [ShortDescription], [Extent5].[Iso] AS [Iso]
FROM [dbo].[ProductLanguage] AS [Extent4]
INNER JOIN [dbo].[Language] AS [Extent5] ON [Extent4].[LanguageId] = [Extent5].[Id] ) AS [Join3] ON ([Limit1].[Product] = [Join3].[ProductId]) AND (([Join3].[Iso] = @p__linq__2) OR (1 = 0))
ORDER BY [Id] ASC, [Id1] ASC, [Category] ASC, [Product] ASC, [C1] ASC
Loading
lang-cs