CODEWITHSUNDEEP
c#sqlentity-frameworklinq
David Létourneau
David Létourneau

Reputation: 1250

Translate a SQL query to linq to entity with a cross join

I have to translate this SQL query to a linq to entity expression in C#. Usually I use Linqer to help me with complexe query, but it doesn't work because of the cross join. I read that I need SelectMany for the cross join but I can't get this done myself.

Here the expression:

select allds.DiscountId, allds.SKUId, max(case when ds.DiscountId is not null then 1 else 0 end) as HasPair
from (select distinct discount.Id as DiscountId, sku.Id as SKUId
      from Discounts discount cross join
           SKUs sku
     ) as allds left outer join
     DiscountSKUs ds
     on allds.DiscountId = ds.DiscountId and allds.SKUId = ds.SKUId
group by allds.DiscountId, allds.SKUId

The query will return a matrix like this:

            10% discount | 15% discount | 25% discount
   SKU #1     Checked         NULL            NULL
   SKU #2     NULL            Checked         NULL
   SKU #3     Checked         NULL            Checked

Thank you for your help!!

Upvotes: 0

Views: 120

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205849

There is no special "cross join" operator in LINQ, the construct is simple no join like this

from a in tableA
from b in tableB
...

Your SQL query should be translated to something like this (not tested) 

var query =
from allds in (from discount in db.Discounts
               from sku in db.SKUs
               select new { DiscountId = discount.Id, SKUId = sku.Id }
              ).Distinct()
join ds in db.DiscountSKUs
on new { allds.DiscountId, allds.SKUId } equals new { ds.DiscountId, ds.SKUId }
into discountSKUs
from ds in discountSKUs.DefaultIfEmpty()
group new { allds, ds } by new { allds.DiscountId, allds.SKUId } into g
select new
{ 
    g.Key.DiscountId,
    g.Key.SKUId,
    HasPair = g.Any(e => e.ds != null)
};

Upvotes: 1

Related Questions