LINQ data from view model using nested SQL

Question

I can access the data using the following TSQL:

select Sweets.*, Qty
from Sweets
left join (select SweetID,  Qty from carts where CartID = '7125794e-38f4-4ec3-b016-cd8393346669' ) t 
  on Sweets.SweetID = t.SweetID

But I am not sure of how to achieve the same results on my web application. Does anyone know how this could be achievable using LINQ?

So far i have:

 var viewModel = new SweetViewModel
 {

   Sweet = db.Sweets.Where(s => db.Carts.Any(c => c.SweetID == s.SweetID))

 };

Edit: Sorry I should of specified that I am using a View model of the 2 classes:

View model:

public class SweetViewModel
{

    public IEnumerable<Sweet> Sweet { get; set; }
    public IEnumerable<Cart> Cart { get; set; }

    //public Cart OrderQty { get; set; }

}

public class Sweet
{
    public int SweetID { get; set; }

    public int CategoryID { get; set; }
    public Category Category { get; set; }
    public string SweetName { get; set; }
    public bool Singular { get; set; }
    public string Description { get; set; }
    public decimal Price { get; set; }


    public virtual ICollection<Cart> Carts { get; set; }
}

public class Cart
{
    [Key]
    public int RecordID { get; set; }
    public string CartID { get; set; }
    public int SweetID { get; set; }
    public int PemixID { get; set; }
    public int Qty { get; set; }
    public System.DateTime DateCreated { get; set; }

    public Sweet Sweet { get; set; }
    public PreMix PreMix { get; set; }

}

Sumit raj · Answer 1 · 2018-07-16T14:35:13.793

0

var res=(from sweet in db.Sweets
         join cart in db.Carts.Select(x=>new{x.SweetID,x.Qty})
         on sweet.SweetID equals cart.SweetID
         into r11
         from r1 in r11.DefaultIfEmpty()
         select new {sweet,r1})
         .Select(x=>new 
                    {
                     Sweet=x.sweet,
                     Qty=x.r1?.Qty
                    }) 
         .ToList();

This will get you the equivalent result to your sql query.

res will be List<a> where a is anonymous class and it's structure will be {Sweet,Qty}.

edited Jul 16 '18 at 14:35

answered Jul 16 '18 at 12:09

Sumit raj

821
1
7
14

The one who is downvoting ..can you please mention why? – Sumit raj Jul 16 '18 at 12:17
Not me down voting but this solution hasn't worked for me, It is showing the error SweetID does not exist in current context, same error for cart – DarrenB Jul 16 '18 at 12:38
Can you post your class `Sweet` and `Cart` – Sumit raj Jul 16 '18 at 12:39
My Bad, i have modified the method, had missed a piece of code. @DarrenB – Sumit raj Jul 16 '18 at 13:03
Still complaining about Qty not in context. Im using var viewModel = new SweetViewModel { sweet = your solutin } – DarrenB Jul 16 '18 at 13:55
var viewModel = new SweetViewModel { Sweet = mysol.Select(x=>x.sweet).ToList(); }; – Sumit raj Jul 16 '18 at 14:05
I have made a change...I don’t know hw i missed this. Sorry I corrected it – Sumit raj Jul 16 '18 at 14:12

score 0 · Answer 2 · answered Jul 16 '18 at 12:13

0

The following will work

from sweet in db.Sweets
join cart in db.Carts 
on sweet.SweetID equals cart.SweetID into swct
from sc in swct.DefaultIfEmpty()
select new { Sweet = sweet, Qty = sweet.Key == sc.Key ? sc.Qty : 0 }

answered Jul 16 '18 at 12:13

Ajay Gupta

1,775
1
10
22

Error CS0266 Cannot implicitly convert type 'System.Linq.IQueryable<>' to 'System.Collections.Generic.IEnumerable'. An explicit conversion exists (are you missing a cast?) – DarrenB Jul 16 '18 at 12:35
This query won't return a list of `sweet` object but a list of new object with properties `sweet` and `qty` – Ajay Gupta Jul 16 '18 at 13:29

Manuel Hoffmann · Answer 3 · 2018-07-16T14:36:55.150

You should be using the LINQ join function.

For my example, I have also used an altered version of your SQL query which I believe to be identical:

SELECT sweets.*, carts.Qty
FROM sweets LEFT JOIN carts ON sweets.SweetID = carts.SweetID
WHERE carts.CartID = '7125794e-38f4-4ec3-b016-cd8393346669'

Then I translated this new query to LINQ with the JOIN function.

var cartId = '7125794e-38f4-4ec3-b016-cd8393346669'
var query = db.Sweets    // table in the "from" statement
   .GroupJoin(db.Carts, // all carts for that sweet will be joined into [sweets -> Cart[]]
      cart => cart.SweetID,        // the first part of the "on" clause in an sql "join" statement
      sweet => sweet.SweetID,   // the second part of the "on" clause)
      (sweet, carts) => new { Sweet = sweet, Carts = cart }) // create new compound object
   .SelectMany(
              sweetsCarts => sweetsCart.Carts.DefaultIfEmpty(), //show the sweet even if there is no cart
              (x,y) => new { Sweet = x.Sweet, Cart = y });
   .Where(sweetsCart => sweetsCart.Cart.CartID == cartId);    // restrict your cartID

Basically, the Join function makes a list of compound objects that contain a Sweet object and a Cart object with each list entry, hence why you can access sweetsCart.Cart.CartID or sweetsCart.Sweets.SweetID.

The name on the left side of the => can be anything you want by the way, it's just an identifier for LINQ. I chose to call it "sweetsCart".

The GroupJoin with the SelectMany makes it possible to do a Left Join.

The query you have changed it to is different results to the data I am wanting. I basically want to display the number of items that exist in the cart under each sweet. So that sweet may not even exist in that specific cart yet but I still need to display the sweet. Hope that makes sense. — DarrenB, Jul 16 '18 at 12:42
I missed the LEFT JOIN. With that, it should give the same results, right? I have also updated the LINQ query. Doing Left Join is not so easy, but it's possible. The code is not tested, but I suggest you try it out. — Manuel Hoffmann, Jul 16 '18 at 14:38
No this is returning the sweets where they exist in cart. I need the left join onto the nested Query in order to get the list of sweets first that will always be displayed, then add the quantity if they exist. — DarrenB, Jul 18 '18 at 10:34

LINQ data from view model using nested SQL

3 Answers3