LINQ multiple joins with a Group By not Working

Question

After trying a lot i couldn't get this to work
I have below query:

var query =  from citiez in db.cities 
             join site in db.sites on citiez.city_id equals site.city_id 
             join ords in db.orders on site.site_id equals ords.site_id 
             group site by site.site_id into grouped 
             select new { 
                 sit = grouped.Count(), 
                 cits = grouped.FirstOrDefault().orders
                       .Where(o => o.site.city.city_name == city) 
             }; 
var list = query.ToList();

It works fine but gives Circular reference error. I have searched it but couldn't get this to work in my case

What i am trying to do in SQL is:

SELECT s.site_id, COUNT(o.order_id) TotalOrders  
FROM city c  
INNER JOIN site s ON c.city_id = s.city_id  
INNER JOIN dbo.[order] o ON s.site_id = o.site_id  
WHERE c.city_id = 4  
GROUP BY s.site_id

The Query returns the desired result in SSMS.

EDIT
This is my Controller Action code:

public ActionResult draw_chart(string city)
        {
            var query = from citiez in db.cities
                        join site in db.sites on citiez.city_id equals site.city_id
                        join ords in db.orders on site.site_id equals ords.site_id
                        group site by site.site_id into grouped
                        select new
                        {
                            sit = grouped.Count(),
                            cits = grouped.FirstOrDefault().orders
                                  .Where(o => o.site.city.city_name == city)
                        };
            var list = query.ToList();
            return Json(list, JsonRequestBehavior.AllowGet);
        }

Any help would be much Appreciated.

_"It works fine but gives Circular reference error."_ Uh? :D — Tim Schmelter, Mar 22 '18 at 08:40
@TimSchmelter I am still getting this error. A circular reference was detected while serializing an object of type 'System.Data.Entity.DynamicProxies. — Azhar Hussain, Mar 22 '18 at 08:45
Perhaps this [response](https://stackoverflow.com/a/16954302/9445781) can help you, it seems that the issue is the same. — Oxald, Mar 22 '18 at 08:49
That LINQ query seems off. You first join orders, but don't use it in the grouping construct. Instead you *again* access orders, this time not with a join. This raises several questions, one of them being why you use a join in the first place. — Sefe, Mar 22 '18 at 08:50
just out of curiosity, did you try something like from citiez in db.cities join site in db.sites.Where(o => o.site.city.city_name == city) on citiez.city_id equals site.city_id join ords in db.orders on site.site_id equals ords.site_id group site by site.site_id into grouped select new { sit = grouped.Count(), cits = grouped.FirstOrDefault().orders }; — Muhammad Umar Farooq, Mar 22 '18 at 09:02

score 0 · Answer 1 · answered Mar 22 '18 at 08:54

0

That Linq doesn't look like the SQL you showed and most of the time you don't need to use JOIN (provided you have a good database design with relations setup). Based on your SQL you can use a Link query like this:

var result = db.Orders
    .Where(o => o.City_id == 4)
    .GroupBy(o => o.Site.Site_id)
    .Select(g => new { 
         Site_id = g.Key, 
         TotalOrders = g.Count 
       });

answered Mar 22 '18 at 08:54

Cetin Basoz

22,495
3
31
39

i have city_name field in city table, city_id in site table and site_id in order table. I want to get Count() of all the orders in that specific site_id but i have city_name only. – Azhar Hussain Mar 22 '18 at 08:57
I don't understand your comment, maybe you should give your schema along with some sample data and your expected output. (o.Site.Cities.SingleOrDefault( c => c.City_name == mycity) could be used instead of o.City_id). – Cetin Basoz Mar 22 '18 at 09:19

Sefe · Answer 2 · 2018-03-22T09:33:50.027

0

What you intend can be expressed in this query:

var query =  from citiez in db.cities
             where citiez.city == city
             from site in citiez.sites
             select new {
                 sit = site.orders.count,
                 cits = site.orders
             };

That would give you the orders for each site.

edited Mar 22 '18 at 09:33

answered Mar 22 '18 at 08:58

Sefe

13,731
5
42
55

Still getting the same error. A circular reference was detected while serializing an object of type 'System.Data.Entity.DynamicProxies. – Azhar Hussain Mar 22 '18 at 09:00

score 0 · Accepted Answer · answered Mar 22 '18 at 09:01

Try this one your controller action. you will get site id and orders for site

public ActionResult draw_chart(string city)
                {
                    var query = from citiez in db.cities
                                join site in db.sites on citiez.city_id equals site.city_id
                                join ords in db.orders on site.site_id equals ords.site_id
                                where  citiez.city_name == city                            
                                group site by site.site_id into grouped
                                select new
                                {

                                    siteId = grouped.Key,
                                    ordersforsite = grouped.Count(),
                                };
                    var list = query.ToList();
                    return Json(list, JsonRequestBehavior.AllowGet);
                }

LINQ multiple joins with a Group By not Working

3 Answers3