1
Select Distinct DVDTitle, CopyNumber from Actors a
inner join CastMembers b
on a.ActorNumber = b.ActorNumber
inner join DVDTitles c
on b.DVDNumber = c.DVDNumber
inner join DVDCopys d
on c.DVDNumber = d.DVDNumber;

How to write this sql query in linq: So far I've done this which returns value twice:

var actorList = from a in _db.Actors
                join b in _db.CastMembers
                on a.ActorNumber equals b.ActorNumber
                join c in _db.DVDTitles
                on b.DVDNumber equals c.DVDNumber
                join d in _db.DVDCopys
                on c.DVDNumber equals d.DVDNumber
                orderby c.DvdTitle
                                   

                select new Actor
                {
                     ActorNumber = a.ActorNumber,
                     ActorSurName = a.ActorSurName,
                     ActorFirstName = a.ActorFirstName,
                     DVDTitle = c.DvdTitle,
                     CopyNumber = d.CopyNumber
                };

I've also tried:

var actorList_01 = actorList.Distinct();

but the result is same.

Samit Paudel
  • 13
  • 4
  • 1
    https://stackoverflow.com/a/34036066/3181933 ? – ProgrammingLlama Apr 19 '22 at 07:27
  • Add `Distinct()`. – Svyatoslav Danyliv Apr 19 '22 at 08:05
  • @SvyatoslavDanyliv it didnot work – Samit Paudel Apr 19 '22 at 08:07
  • Your original query performs a `DISTINCT` on `DVDTitle` and `CopyNumber`, not a whole `Actor`. – Jeroen Mostert Apr 19 '22 at 08:09
  • So how can I do this? – Samit Paudel Apr 19 '22 at 08:11
  • Just select only `DVDTitle` and `CopyNumber` in projection. Currently you have 5 fields. – Svyatoslav Danyliv Apr 19 '22 at 09:16
  • First step is to stop treating linq and SQL as the same, and trying in LINQ what you'd do in SQL; if you're going to do that you might as well just use raw SQL. Then, you treat your ORM like it knows the links between entities and will write joins for you, and start from some sensible place; the manymost end of all your 1:many relationships is a reasonable place to start because you can just straight access a parent item by its single property without having to dig through a collection, and you do eg `_db.DVDCopys.Select(c => new{ c.CopyNumber, c.DVDTitle.DvdTitle}).Distinct()` – Caius Jard Apr 19 '22 at 10:47
  • Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. – NetMage Apr 22 '22 at 19:45

1 Answers1

0

try this

 var matchingList = actorList 
                .GroupBy(x => x.ActorNumber )
                .Select(g => g.First())
                .ToList();
M Rizwan
  • 83
  • 8