Assistance to convert my SQL query to LINQ?

Question

I need some help to convert this query to LINQ:

SELECT
    st.ProductId, st.ProductType, st.StockValue, st.InOut, st.SupplierCmdId, st.CreationDate,
    (SELECT su.Id FROM Suppliers AS su 
     WHERE su.Id = (SELECT suo.SupplierId FROM SupplierOrders AS suo 
                    WHERE suo.Id = st.SupplierCmdId)) AS SupplerId
FROM 
    StockDetails AS st 
WHERE 
    st.ProductType = 'Yarn' 
    AND st.ProductId = 2835 
ORDER BY 
    st.CreationDate DESC

Output:

ProductId   ProductType StockValue  InOut   SupplierCmdId   CreationDate                SupplerId
2835        Yarn        10          1       1450            2020-03-12 15:25:54.000     151
2835        Yarn        5           0       NULL            2019-03-04 00:00:00.000     NULL
2835        Yarn        5           0       NULL            2018-12-23 00:00:00.000     NULL
2835        Yarn        10          1       1398            2018-12-17 10:51:17.000     151

Thanks in advance

I have tried:

var ProductType = "Yarn";
var ProductId = 2835;

stocks = (from st in _context.StockDetails
join sn in _context.StockStatus on st.StatusId equals sn.Id
where st.ProductId == ProductId 
      && st.ProductType == ProductType 
orderby st.CreationDate descending
select new StockList
    {
    StockValue = st.StockValue,
    InOut = st.InOut,
    SupplierCmdId = st.SupplierCmdId,
    CreationDate = st.CreationDate
    });

On this I need to find the Suppliers Id (see SQL query)

Does this answer your question? [SQL to LINQ Tool](https://stackoverflow.com/questions/296972/sql-to-linq-tool) — Ali Torabi, Nov 03 '20 at 07:49
`var ProductId = "Yarn";` Product id looks like a number in the example data — Pac0, Nov 03 '20 at 08:26
I have no idea why people are always wanting to convert SQL to linq. SQL is a considerably better tool for query databases than linq. Linq especially when used with EF tends to mangle your query making it very hard to optimise. — Liam, Nov 03 '20 at 08:37
@Liam - Trying to find my way here. I'm more confortable with SQL, but it seems like using .net and EF, linq has some advantages https://stackoverflow.com/questions/593808/what-are-the-advantages-of-linq-to-sql — Philippe, Nov 03 '20 at 08:59

score 1 · Accepted Answer · answered Nov 03 '20 at 09:03

1

I believe this should be equivalent:

var productType = "Yarn";
var productId = 2835;
var query =
    from st in ctx.StockDetails
    where st.ProductType == productType
    where st.ProductId == productId
    orderby st.CreationDate descending
    let suppliers =
        from suo in ctx.SupplierOrders
        join su in ctx.Suppliers on suo.SupplierId equals su.Id
        where suo.Id == st.SupplierCmdId
        select su
    from su in suppliers.DefaultIfEmpty()
    select new
    {
        st.ProductId,
        st.ProductType,
        st.StockValue,
        st.InOut,
        st.SupplierCmdId,
        st.CreationDate,
        SupplierId = su.Id,
    };

answered Nov 03 '20 at 09:03

Jeff Mercado

129,526
32
251
272

Thanks @Jeff Mercado. It gives me a CS1941 C# The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'Join'. – Philippe Nov 03 '20 at 09:24
I had to move up the let before the where clause and it works perfectly. Thanks again @Jeff Mercado – Philippe Nov 04 '20 at 13:15

Assistance to convert my SQL query to LINQ?

1 Answers1