CODEWITHSUNDEEP
linqlambda
user2004403
user2004403

Reputation: 233

Convert query to lambda

I want to know how can I write this query:

var query = from p in context.DimProduct
        from psc in context.DimProductSubcategory
        // on psc.ProductCategoryKey equals pc.ProductCategoryKey
        where psc.EnglishProductSubcategoryName == subCategoryName
              && psc.ProductSubcategoryKey == p.ProductSubcategoryKey
        select new DimProductDTO
                   {
                       ProductKey = p.ProductKey,
                       ProductSubcategoryKey = p.ProductSubcategoryKey,
                       EnglishProductName = p.EnglishProductName,
                       Size = p.Size,
                       StandardCost = p.StandardCost
                   };

I tried some queries, but no success. My problem is that I don't know how to have access to DimProduct and DimProductSubcategory.

Any suggestions?

Upvotes: 0

Views: 60

Answers (2)

MarcinJuraszek
MarcinJuraszek

Reputation: 125610

context.DimProduct
       .SelectMany(p => new { p, psc = context.DimProductSubcategory })
       .Where(x => x.psc.EnglishProductSubcategoryName == subCategoryName
              && x.psc.ProductSubcategoryKey == x.p.ProductSubcategoryKey)
       .Select(x => new DimProductDTO {
                       ProductKey = x.p.ProductKey,
                       ProductSubcategoryKey = x.p.ProductSubcategoryKey,
                       EnglishProductName = x.p.EnglishProductName,
                       Size = x.p.Size,
                       StandardCost = x.p.StandardCost })

However, you're not selecting anything from DimProductSubcategory, so I think the same can be done using Any() extension method:

context.DimProduct
       .Where(x => context.DimProductSubcategory
                          .Any(y => y.EnglishProductSubcategoryName == subCategoryName
                                 && y.ProductSubcategoryKey == x.ProductSubcategoryKey))
       .Select(x => new DimProductDTO {
                       ProductKey = x.ProductKey,
                       ProductSubcategoryKey = x.ProductSubcategoryKey,
                       EnglishProductName = x.EnglishProductName,
                       Size = x.Size,
                       StandardCost = x.StandardCost });

It should generate IN SQL statement within the query.

Upvotes: 1

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236188

That is not exactly same query, but it produces same result via inner join (I believe that is more efficient than cross join)

context.DimProduct
       .Join(context.DimProductSubcategory
                 .Where(x => x.EnglishProductSubcategoryName == subCategoryName),
             p => ProductSubcategoryKey,
             psc => ProductSubcategoryKey,
             (p,psc) => new { p, psc })
       .Select(x => new DimProductDTO {
                       ProductKey = x.p.ProductKey,
                       ProductSubcategoryKey = x.p.ProductSubcategoryKey,
                       EnglishProductName = x.p.EnglishProductName,
                       Size = x.p.Size,
                       StandardCost = x.p.StandardCost })

Also your original query can be rewritten as

var query = from p in context.DimProduct
            join psc in context.DimProductSubcategory
               on p.ProductSubcategoryKey equals psc.ProductSubcategoryKey
            where psc.EnglishProductSubcategoryName == subCategoryName
            select new DimProductDTO {    
                   ProductKey = p.ProductKey,
                   ProductSubcategoryKey = p.ProductSubcategoryKey,
                   EnglishProductName = p.EnglishProductName,
                   Size = p.Size,
                   StandardCost = p.StandardCost
            };

Generated SQL will look like:

SELECT [t0].[ProductKey], [t0].[ProductSubcategoryKey]
FROM [DimProduct] AS [t0]
INNER JOIN [DimProductSubcategory] AS [t1] 
    ON [t0].[EnglishProductSubcategoryName] = [t1].[ProductSubcategoryKey]
WHERE [t1].[EnglishProductSubcategoryName] = @p0

Upvotes: 1

Related Questions