CODEWITHSUNDEEP
c#sqlentity-framework-4
Steven Combs
Steven Combs

Reputation: 1939

How do I make Entity Framework not join tables?

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.

Upvotes: 1

Views: 723

Answers (5)

Michael Buen
Michael Buen

Reputation: 39393

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

Upvotes: 0

Mert Akcakaya
Mert Akcakaya

Reputation: 3129

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.

Upvotes: 0

JotaBe
JotaBe

Reputation: 39014

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.

Upvotes: 1

Magnus
Magnus

Reputation: 46919

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 
                          };

Upvotes: 1

Jonathan
Jonathan

Reputation: 12025

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.

Upvotes: 0

Related Questions