locked
how to translate sql nested statement into linq RRS feed

  • Question

  • User1777367607 posted

    Hi 

    I am using Entity Framework and .net I wanted to know the best way of returning the products table which uses the buy price column to calculate a discount value from a separate table.

    The sql would look like this

    select * ,    
    BuyPrice / (select DiscountValue From discountBands where DiscountKey='A')   As BandA,
    BuyPrice / (select DiscountValue From discountBands where DiscountKey='B')   As BandB,
    BuyPrice / (select DiscountValue From discountBands where DiscountKey='C')   As BandC
    from products

    // return _context.Products.OrderBy(c => c.ProdName).ToList();



    any ideas please?

    Tuesday, October 30, 2018 9:38 PM

All replies

  • User1120430333 posted

    If using EF 6, then you could use the EF backdoor, run a stored procedure, return the result and use a datareader to populate products object on the EF model loading a collection of products.

    You can then sort the collection or do the sort in the sproc. 

    https://blogs.msdn.microsoft.com/alexj/2009/11/07/tip-41-how-to-execute-t-sql-directly-against-the-database/

    If using EF 6, then you'll have to use the IObjectContextAdapter.to get the connection.

    Wednesday, October 31, 2018 3:22 AM
  • User1520731567 posted

    Hi SNandra,

    The sql would look like this

    select * ,    
    BuyPrice / (select DiscountValue From discountBands where DiscountKey='A')   As BandA,
    BuyPrice / (select DiscountValue From discountBands where DiscountKey='B')   As BandB,
    BuyPrice / (select DiscountValue From discountBands where DiscountKey='C')   As BandC
    from products

    According to your requirement,I think you could get DiscountValueA,DiscountValueB,DiscountValueC firstly,and then select new list.

    For example:

      var DiscountValueA= db.discountBands.Where(_ => _.DiscountKey== "A");
    var DiscountValueB= db.discountBands.Where(_ => _.DiscountKey== "B");
    var DiscountValueC= db.discountBands.Where(_ => _.DiscountKey== "C");

    var list = db.products.Select(a => new { Id = a.Id, 
    xxx= a.xxx,
    ...
    BandA = a.BuyPrice/DiscountValueA,
    BandB = a.BuyPrice/DiscountValueB,
    BandC = a.BuyPrice/DiscountValueC
    }).ToList();

    Best Regards.

    Yuki Tao

    Wednesday, October 31, 2018 7:24 AM