How do I make Entity Framework not join tables?

Question

Ok so I am testing out EF once again for performance and I just want a simple result back from my database.

Example

var jobsList = from j in mf.Jobs
                       where j.UserID == 1001 select new { Job = j };

This unfortunately joins my User object to this list, which I don't want EF to do. How do I tell EF not to join just because there is a relationship. Basically I just want a simple row from that table.

Or do I need to use a different type of retrieval. I am still using the basic type of database retrieval below and I feel there are better ways to handle db work by now.

SqlConnection myconnection = new SqlConnection();

Edit

Basically what I am saying in a more clearer context. Is that instead of only getting the following.

Job.JobID
Job.UserID
//Extra properties

I Get

Job.JobID
Job.UserID
Job.User
//Extra properties

That User object easily consumes way more memory than is needed, plus I don't need it.

My Solution

So I am still not believing in EF too much and here is why. I turned off LazyLoading and turned it on and didn't really notice too much of a performance difference there. I then compared the amount of data that my SqlConnection type method uses compared to my EF method.

I get back the exact same result set and here are the performance differences.

For my Entity Framework method I get back a list of jobs.

MyDataEntities mf = new MyDataEntities(); // 4MB for the connection...really?
mf.ContextOptions.LazyLoadingEnabled = false;
// 9MB for the list below
var test = from j in mf.Jobs
           where j.UserID == 1031
           select j;
foreach (Job job in test) {
     Console.WriteLine(job.JobID);
}

For my SqlConnection method that executes a Stored Proc and returns a result set.

//356 KB for the connection and the EXACT same list.
List<MyCustomDataSource.Jobs> myJobs = MyCustomDataSource.Jobs.GetJobs(1031);

I fully understand that Entity Framework is doing way way more than a standard SqlConnection, but why all this hype if it is going to take at a minimum of 25x more memory for a result set. Just doesn't seem worth it.

My solution is not to go with EF after all.

Is lazy loading enabled? If not, the join may be because you're eager-loading the User property. — w.brian, May 08 '12 at 21:55
What do you mean with "this unfortunately joins my User object to this list"? — JotaBe, May 08 '12 at 21:55
Why is there a join? Isn't `UserID` just a foreign key column in the `Jobs` table and the SQL has a simple WHERE clause for this column? — Slauma, May 08 '12 at 21:55
@w.brian I am not sure what lazy loading is. I will look more into that. — Steven Combs, May 08 '12 at 22:00
Do you really mean to include Job in a new anonymous class with a `Job` property? Can't you select `j` directly? — JotaBe, May 08 '12 at 22:13
@JotaBe yeah I shouldn't have done that. Selecting j worked fine. — Steven Combs, May 09 '12 at 15:10

score 1 · Accepted Answer · answered May 08 '12 at 22:09

1

The User property is part of the job class but wont be loaded until you access it (lazy loading). So it is not actually "joined".

If you only want the two specified columns you can write

var jobsList = from j in mf.Jobs
               where j.UserID == 1001 
               select new { 
                          Job.JobID, 
                          Job.UserID 
                          };

answered May 08 '12 at 22:09

Magnus

45,362
8
80
118

Ok ty i think this is what i need. Basically if I want to return a specific class and only that class or in this case table, I need to specify field by field what I want. – Steven Combs May 08 '12 at 23:47
1

@meanbunny If you want to return all the values from table job just write `... select job` releated tables will show up on the object but wont be loaded. – Magnus May 09 '12 at 07:54
Ahhhh I see now. After testing this out it makes perfect sense. Ty for your help. – Steven Combs May 09 '12 at 14:22

score 1 · Answer 2 · answered May 08 '12 at 22:11

The most probable reason for this behavior is that you have LazyLoadingEnabled property set to true.

If this is the case, the User isn't recovered in the original query. But if you try to acces this property, even if you do it through an inspection while debugging, this will be loaded from the database. But only if you try to access it.

You can check this opening a a SQL Server Profiler, and seeing what commands are begin sent to the DB.

Your code is not using eager loading or explicit loading. So this must be the reason.

score 0 · Answer 3 · answered May 08 '12 at 21:57

0

I think that EF don't know that you want one result only. Try something like this.

Job jobsItem = mf.Jobs.Single(j=>j.UserID==1001)

If you don't want to use lambas...

Job JobItem = (from j in mf.Jobs where j.UserID == 1001 select j).Single()

I haven't a compiler near right now, I hope the syntax is right. Use can use var instead of Job for your variable if you preffer. It has no effect but I think Job is more readable in this case.

answered May 08 '12 at 21:57

Jonathan

11,809
5
57
91

I think the syntax is perfect here. However, checkout my edit and see more about what I am saying. Sorry for the incomplete description at first. Basically I would only want UserID back not UserID and User. – Steven Combs May 08 '12 at 22:05
Ok :D I've read your edit. Then I think it's all about lazy loading concept. Take a look to it like JotaBe recomend you. – Jonathan May 09 '12 at 08:20

score 0 · Answer 4 · answered May 08 '12 at 22:17

0

User actually is not attached to context until you access User property of Job. Turn off Lazy Loading if you want to get a null for User.

answered May 08 '12 at 22:17

Mert Akcakaya

3,109
2
31
42

score 0 · Answer 5 · edited May 23 '17 at 11:48

0

Entity Framework does not support lazy loading of properties. However, it has table-splitting

Emphasized the properties. Of course, Entity Framework supports lazy loading of rows

edited May 23 '17 at 11:48

Community

1
1

answered May 08 '12 at 22:34

Michael Buen

38,643
9
94
118

How do I make Entity Framework not join tables?

5 Answers5