0

I am new in linq and struggling with it.

I have T-SQL query and am trying to convert a linq query. I have some problems with joining tables.

Here the T-SQL query:

    SELECT CAST(CREATE AS DATE) CREATEDATE
           A.ID,
           100 AS PRODUCTTYPE,
           SUM(ELEMENTCOUNT) AS NUMOFITEMS,
           SUM(ELEMENTCOUNT)*(CASE WHEN A.USERID=C.USERID AND PRODUCTTYPE=100 THEN C.PRICE ELSE 10 END) AS TOTALPRICE
    FROM PRODUCTLIST A LEFT JOIN PRICELIST C ON A.USERID=C.USERID
    WHERE CAST(CREATE AS DATE)>='2021-01-01'
    GROUP BY CAST(CREATE AS DATE), A.ID

And here the C# code:

 var soldProducts = _context.ProductList
            .Where(x => x.Created.Value.Date >= "2021-01-01")
            .GroupBy(t => new { t.Created.Value.Date, t.UserId})
            .Select(i => new SoldProductList()
            {
                Date = i.Key.Date,
                UserId = i.Key.UserId,
                ProductType = 100,
                NumOfItems = i.Sum(y => y.ElementCount),
                TotalPrice = i.Sum(y => y.ElementCount * ( **??**  ))
            }).ToList();

In the y.ElementCount * ( **??** )part I want to say that if the UserId in ProductList equals to the UserId in PriceList, take this price; else take the price as 10. So I need to join ProductList and PriceList.

I try to implement GroupJoin but can not understand the resultSelector part, Why am I need result selector, if I need how can I implemet in this code.

simoncare
  • 75
  • 1
  • 8
  • 1
    You can refer to this if it helps [C#-Left Outer Join](https://stackoverflow.com/questions/3404975/left-outer-join-in-linq) – Bilal Bin Zia Feb 23 '21 at 09:05
  • Does this answer your question? [LINQ: combining join and group by](https://stackoverflow.com/questions/9173410/linq-combining-join-and-group-by) – Self Feb 23 '21 at 09:45
  • @BilalBinZia, you're right but I need something with method syntax, not query syntax. – simoncare Feb 23 '21 at 10:11
  • @simoncare There are answers on that thread where they use method syntax with lambda expression. – Peter Csala Feb 23 '21 at 12:57

0 Answers0