How to convert T-SQL to LINQ?

Question

How can I convert the following T-SQL query, that produces correct results, to LINQ expression in C#?

WITH    CTE
          AS ( SELECT RN = ROW_NUMBER() OVER ( PARTITION BY dbo.Product.ID ORDER BY dbo.Picture.UpdatedDate ASC )
                   ,Product.ID
                   ,Product.Name
                   ,Picture.Path
                FROM Dbo.Product
                INNER JOIN dbo.Product_Picture_Mapping
                    ON dbo.Product_Picture_Mapping.ProductID = dbo.Product.ID
                INNER JOIN dbo.Picture
                    ON dbo.Picture.ID = dbo.Product_Picture_Mapping.PictureID)
    SELECT ID AS 'ID'
           ,Name AS 'Name'
        FROM CTE
        WHERE RN = 1

I have tried the following code.

var result= (from c in cd.Product
              join pp in cd.Product_Picture_Mapping
                            on c.ID equals pp.ProductID
                        join pcc in cd.Picture
                            on pp.PictureID equals pcc.ID
                        select new ProductViewModel() { PicturePath = pcc.Path, ProductName = c.Name, ProductID = c.ID}).ToList();

What do you get? An exception? Wrong data? Your column outputs are different between the two queries for starters. — D Stanley, Feb 20 '14 at 19:25
so, i have only one picture which name is product1. product1 is have 4 pictures, i get only 1 row via sql query (this is what i want) but i get 4 rows via lambda expression this is problem for me. i want only 1 row — Dursun ICCAN, Feb 20 '14 at 19:29
possible duplicate of [Row\_number over (Partition by xxx) in Linq?](http://stackoverflow.com/questions/9980568/row-number-over-partition-by-xxx-in-linq) — , Feb 20 '14 at 19:51

score 0 · Accepted Answer · answered Feb 20 '14 at 20:22

See if the following works:

var result= (from c in cd.Product
              join pp in cd.Product_Picture_Mapping
                            on c.ID equals pp.ProductID
              let pcc = pp.Pictures.FirstOrDefault()
              select new ProductViewModel() 
              {
                  PicturePath = pcc.Path,
                  ProductName = c.Name,
                  ProductID = c.ID
              }).ToList();

Looking at your table names, it looks like you have a many-many table (product_picture_mapping) which EF may be able to optimize away giving you the option of doing something like:

var result = from c in cd.Product
             let ppc = c.Pictures.FirstOrDefault()
             select new ProductViewModel 
             {
                 PicturePath = pcc.Path, 
                 ProductName = c.Name, 
                 ProductID = c.ID
             };

How to convert T-SQL to LINQ?

1 Answers1