Linq query showing more data than the SQL alternative

Question

I have an linq query like this :

var query = from Romm in RoMM
                        
                        join rfrsa in RoMmfrsa  on Romm.RoMmid equals rfrsa.RoMmid
                        join frsa in Frsa on rfrsa.Frsaid equals frsa.Fraid
                        join fra in Fra on frsa.Fraid equals fra.Fraid
                        where Romm.ActTypeId == 2 && Romm.SegmentId == 4
                        select new
                        {
                            Romm.ActTypeId,
                            Romm.RoMmid,
                            frsa.Fraid,
                            frsa.Frsaid,
                            Romm.ImpactId
                        };

And I have SQL code as below :

SELECT romm.ROMMID 

     , frsa.FRAID 

     , frsa.FRSAID 

     , romm.ImpactID 

  FROM RoMM AS romm 

 INNER 

  JOIN RoMMFRSA AS rfrsa 

    ON romm.RoMMID = rfrsa.RoMMID 

 INNER 

  JOIN FRSA AS frsa 

    ON rfrsa.frsaid = frsa.frsaid 

 INNER 

  JOIN FRA AS fra 

    ON frsa.FRAID = fra.FRAID 

 WHERE romm.acttypeid = 2 

   AND romm.segmentid = 4

The SQL only shows one row (which is correct), the linq shows the correct row and then it displays about another 3 rows which is not what we need. I need the linq to show one row which is correct with the SQL. Is this because of maybe many-many relationships ?

We can't answer the question, cause we don't know the database structure, nor we have any example data to reproduce the problem. Maybe take a look at [this question](https://stackoverflow.com/questions/18237312/get-sql-query-from-linq-to-sql) to get the Query that will be send to your db and compare it to your original query to find the difference. — Oliver, Sep 29 '21 at 10:29
`join frsa in Frsa on rfrsa.Frsaid equals frsa.Fraid` join condition is not the same as SQL join condition. Because of your choice of naming it's hard to differentiate what id belongs to what. — Eldar, Sep 29 '21 at 10:32
`ON rfrsa.frsaid = frsa.frsaid` vs `rfrsa.Frsaid equals frsa.Fraid` - typo on fraid/frsaid? — Caius Jard, Sep 29 '21 at 10:33
@CaiusJard can you make yours the answer please - i missed that ... wonderful — Jason_Hough, Sep 29 '21 at 10:36
it looks as if the column names are exactly designed to elicit such typo mistakes. Almost like a psycho linguistic experiment. or a riddle.... — Mong Zhu, Sep 29 '21 at 11:47

score 3 · Accepted Answer · answered Sep 29 '21 at 10:39

3

Looks like a typo in either the C# or the SQL join:

SQL: ON rfrsa.frsaid    =   frsa.frsaid
C#:     rfrsa.Frsaid equals frsa.Fraid
                                 ^^^^^^ 
                              mismatch here

answered Sep 29 '21 at 10:39

Caius Jard

72,509
5
49
80

Linq query showing more data than the SQL alternative

1 Answers1