1

I am facing a big problem with simple linq query.. I am using EF 4.0.. I am trying to take all the records from a table using a linq query:

var result = context.tablename.select(x=>x);

This results in less rows than the normal sql query which is select * from tablename;

This table has more than 5 tables as child objects (foreign key relations: one to one and one to many etc)..

This result variable after executing that linq statement returns records with all child object values without doing a include statement..

I don't know is it a default behavior of EF 4.0 ..

I tried this statement in linqpad also..but there is no use...

But interesting thing is if I do a join on the same table with another one table is working same is sql inner join and count is same..but I don't know why is it acting differently with that table only..

Is it doing inner joins with all child tables before returning the all records of that parent table??

please help me..

marc_s
  • 675,133
  • 158
  • 1,253
  • 1,388
Sunny43
  • 109
  • 4
  • 15
  • If you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! – marc_s Mar 19 '11 at 10:05
  • 1
    thanks..i will do from next time.. – Sunny43 Mar 19 '11 at 20:31

2 Answers2

0

The easiest way to troubleshoot these types of issues is to look at the generated SQL produced by the ORM tool.

If you are using SQL Server then using the SQL Profiler to view the generated SQL.

From what you are describing, a possible explanation might be that your relationships between entities are mandatory and thereby enforcing INNER joins instead of LEFT OUTER joins.

Philip Fourie
  • 96,182
  • 10
  • 57
  • 81
  • "Enforcing FULL OUTER join" seems a mistake. Did you mean INNER join? – George Polevoy Mar 19 '11 at 10:15
  • @George Poleyvoy, you are correct I updated my answer accordingly. – Philip Fourie Mar 19 '11 at 11:03
  • i don't have a sql profiler on my machine and i am unable check profiler results right now..but i will update my results on monday..but i did a tracing using Visual studio 2010 to check the sql query executing for that linq query..but its same as normal sql query without any joins with any of the child tables..is there anyway to switch off the child tables loading on selecting a parent table..thanks a lot for your help.. – Sunny43 Mar 19 '11 at 19:59
0

This table has more than 5 tables as child objects (foreign key relations: one to one and one to many etc)..

This result variable after executing that linq statement returns records with all child object values without doing a include statement..

So we are probably talking about database view or custom DefiningQuery in SSDL.

I described the same behavior here. Your entity based on joined tables probably doesn't have unique identification for each retruned row so your problem is Identity map. You must manually configure entity key of your entity. It should be composite key based on all primary keys from joined tables. Entity key is used to identify entity in indenty map. If you don't have unique key for each record only first record with the new key is used. If you didn't specify the key manually EF had infered its own.

Community
  • 1
  • 1
Ladislav Mrnka
  • 349,807
  • 56
  • 643
  • 654
  • all child tables have their own primary key id and a foreign key id which is related to primary key of parent table..most of the relations are one to many.. – Sunny43 Mar 19 '11 at 19:57
  • How did you create that `tablename` and how is defined its key? That is the important part. – Ladislav Mrnka Mar 19 '11 at 20:57
  • 1
    i made a dumb mistake..i found that my connection strings are different from my DAL layer to business...thats an end of my three day struggle.. thanks a lot.. – Sunny43 Mar 22 '11 at 14:53