LINQ Query that omits any duplicate IDs

Question

I have two tables. For simplistic reasons will call them Table A and Table B,

Table A:

ID int(PK)

customername varchar

customeraddy varchar

inservice boolean

etc varchar

etc varchar

Table B:

PKID int(PK)

ID int()

linename varchar

Just looking for a LINQ query that can select all values from a joined table of Table A and Table B on the ID column where inservice == "true" that omits any duplicates based on ID (because Table B has multiple duplicates of ID).

So far this is what i have:

from x in db.tableA
join y in db.tableB on x.id equals y.id
where x.inservice == "true"
select y);

Thats something else than _"omitting any duplicates based on ID"_ — Tim Schmelter, Sep 11 '18 at 15:04
Possible duplicate of [Remove duplicates in the list using linq](https://stackoverflow.com/questions/1606679/remove-duplicates-in-the-list-using-linq) — Heretic Monkey, Sep 11 '18 at 15:07
You need to use an left outer join to get items in tableB that is not in table A : https://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b — jdweng, Sep 11 '18 at 15:27
You need to `GroupBy` ID and then use `Count` to select the groups where the count is 1. — NetMage, Sep 11 '18 at 16:05
Hey @NetMage can you elaborate more on that? i feel like that's definitely the right way to go but i'm not too experienced with doing that using LINQ — user10192547, Sep 11 '18 at 16:26

score 1 · Answer 1 · answered Sep 11 '18 at 17:39

1

Assuming inservice is bool as in your table description and not string, and assuming you want both tableA and tableB rows where tableB has only one matching row,

var AjoinB = from x in db.tableA
             where x.inservice
             join y in db.tableB on x.id equals y.id
             group new { x, y } by x.id into xyg
             where xyg.Count() == 1
             select xyg;

answered Sep 11 '18 at 17:39

NetMage

26,163
3
34
55

So I think the query works fine but i am getting an error saying that i can't implicitly convert type to the type defined in my ViewModel. this would be a ASP.NET MVC issue? – user10192547 Sep 11 '18 at 21:01
@user10192547 I think you will need to ask a separate question or show more code in this question explaining your purpose. – NetMage Sep 11 '18 at 21:35

LINQ Query that omits any duplicate IDs

1 Answers1

Linked