Cannot convert using LINQ grouping

Question

Hi so here's what i have so far... In my controller:

public ActionResult SingleFeed (string linename)
{
    var result = (from x in db.is1
                  where x.in_service == "Y"
                  join y in db.isckts on
                  x.is_id equals y.is_id
                  where y.line_name == linename
                  group new {x, y} by x.is_id into xyg
                  where xyg.Count() == 1
                  select xyg);
    var SFM = new CircuitsViewModel()
    {
        IsDetails = result.ToList()
    };

    return View(SFM);
}

In my view:

public class CircuitsViewModel
{
        public List<is1> IsDetails { get; set; }
        public List<isckt> IscktDetails { get; set; }
}

I end up with a

cannot implicitly convert type 'System.Collections.Generic.List(System.Linq.IGrouping(short, (anonymous type: is1 x, isckt y)))' to 'System.Collections.Generic.List(is1)

Was wondering if anyone could help with this error. Started happening when i tried to using LINQ grouping but i have no idea how to model the Viewmodel or chage the LINQ query to match the type.

The idea behind the LINQ query is to return records that does not have duplicate entries in the id column on table isckts.

UPDATE: Currently trying to figure out how to find duplicates in db.isckts.is_ID then do a join on IS_ID then do a where to specify the line_name and in_service

Any help would be appreciated! cheers!

Change `select xyg` to `select xyg.First().x` - At first I didn't see your anonymous type, therefor you have to access the `x` of the `First()` item in your grouping result. — Rand Random, Sep 12 '18 at 14:49
Still seems to throw the same error after select xyg.First() — user10192547, Sep 12 '18 at 14:51
Hi looks that that made the error go away, but now my query doesn't return what was intended. thanks! i have another thread up for the query. — user10192547, Sep 12 '18 at 15:05

NetMage · Answer 1 · 2018-09-14T18:18:56.697

It looks like you just want the rows from db.is1 that have no duplicate db.isckts matching rows, so you need to just group and return the is1 rows.

var result = from x in db.is1
             where x.in_service == "Y"
             join y in db.isckts on x.is_id equals y.is_id
             where y.line_name == linename
             group x by x.is_id into xg
             where xg.Count() == 1
             select xg.First();

However, since you are just using the grouping to count the joined rows, you can use the LINQ Group Join operator to do this instead:

var AjoinB = from x in db.tableA
             where x.inservice
             join y in db.tableB on x.is_id equals y.is_id into yj
             where yj.Count(y => y.line_name == linename) == 1
             select x;

I switched to lambda syntax to add the condition to the count on y; you could add the condition to db.tableB using the Where method as well, or even create a subquery to represent the filtered db.tableB:

var filtered_isckts = from y in db.isckts
                      where y.line_name == linename
                      select y;
var result = from x in db.is1
             where x.in_service == "Y"
             join y in filtered_isckts on x.is_id equals y.is_id into yj
             where yj.Count() == 1
             select x;

To modify the filtered_isckts to only include ids with one row, do a group by on the first query:

var filtered_isckts = from y in db.isckts
                      where y.line_name == linename
                      group y by y.is_id into yg
                      where yg.Count() == 1
                      select yg.First();
var result = from x in db.is1
             where x.in_service == "Y"
             join y in filtered_isckts on x.is_id equals y.is_id
             select x;

Out of curiosity - Did you notice you where the one suggesting his approach? https://stackoverflow.com/questions/52278800/linq-query-that-omits-any-duplicate-ids — Rand Random, Sep 13 '18 at 06:40
Yes, but the attempt at genericizing the previous question made that answer less useful — NetMage, Sep 13 '18 at 18:14
Hey @NetMage, was wondering say i wanted to apply the third solution you mentioned above. But i wanted to filter table Y on is_id with count > 1 in the var filtered_isckts query before performing a join, what would the syntax be for that? — user10192547, Sep 14 '18 at 14:47
@user10192547 You would have to use group by in the `filtered_isckts` query similar to the first answer, and the point of that query is to avoid doing group by as an extra step when it can be combined with the `join`. — NetMage, Sep 14 '18 at 17:46
I may need a little more of a push... been trying to alter the query but haven't been getting very far. — user10192547, Sep 14 '18 at 18:07
@user10192547 I modified my answer to do the group by in the first query. — NetMage, Sep 14 '18 at 18:19

score 0 · Answer 2 · answered Sep 12 '18 at 14:44

0

Because linq group dont return List<IsDetails>

You should use result.Select(x => new IsDetails {});

answered Sep 12 '18 at 14:44

Onur Vatan

111
1
5

Can you elaborate more on this? do i need to include this in the query... or ...? – user10192547 Sep 12 '18 at 14:53

Cannot convert using LINQ grouping

2 Answers2