0

I am using Entity Framework in my ASP.NET MVC application, and I am facing an issue in loading data from SQL Server via LINQ. My query returns the result in 4 seconds, but I need to get in less time, still searching for better solution.

Here is my linq query: 

var user =
    context.CreateObjectSet<DAL.ProductMaster>()
        .AsNoTracking()
        .Include("Product1").AsNoTracking()
        .Include("Product2").AsNoTracking()
        .Include("Product3").AsNoTracking()
        .Include("Product4").AsNoTracking()
        .Include("Product5").AsNoTracking()

        .Where(x => x.Id == request.Id)
         ).FirstOrDefault();
Improve this question
10
  • 5
    Have you tried loading less entitites? Commented Apr 20, 2017 at 13:26
  • 3
    Are you sure that you need ALL of those other tables and data as that is going to generate quite a lot of JOIN operations, which can be expensive. Additionally, you can replace your Where() clause with FirstOrDefault() to clean things up a bit and you likely don't need the AsNoTracking() call for each individual Include(). Commented Apr 20, 2017 at 13:27
  • 1
    Try writing the sql that you want an executing it against the db manually. If that runs well try to optimize your linq Commented Apr 20, 2017 at 13:27
  • 3
    Consider using the .Select() operator to grab exactly what you want, instead of this hodgepodge of what looks like a large proportion of your database. Commented Apr 20, 2017 at 13:29
  • 1
    Get the data in multiple queries + remove organizationsList.Count()!= 0 (and, not performance-related: remove all but one AsNoTracking calls). Commented Apr 20, 2017 at 13:40

2 Answers 2

Reset to default
0

No need to get multiple entities fulfilling the predicate in Where() only to then select the first one; simply call FirstOrDefault() directly. 

if (request == null || organizationsList == null || !organizationsList.Any())
    return; 

var user = context.CreateObjectSet<DAL.User>()
                  .AsNoTracking()
                  .Include("UsersinPrograms.FollowUps")
                  .Include() ...
                  .FirstOrDefault(x => x.Id == request.userId && organizationsList.Contains(x.OrganizationId)));

Also, remove the Include()'s that you don't need.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

thanks for the code,hard luck am getting the same result like my previous result.
context.CreateObjectSet<DAL.User>().AsNoTracking() ... seems weird, don't you want something like context.Users..AsNoTracking() ... ? you're users should already be created and now you're querying/filtering existing data (not created new dbsets). Or perhaps this is only a badly named method?
seems its must to initiate the table structure, so can't
0

For this and any large scale business logic operations one should create Stored Procedures instead of basing them in Linq. Then in EF one creates a DTO object to handle the result and consumes the procedure in EF.

At that point the speed gained or lost is up to how the procedure's sql has been structured, with an eye on indexes used within the database to possibly provide an enhancement on speed.

Linq SQL is really boilerplate and not designed for speed.

I have done this with multiple EF projects.

Also one can build their linq query in Linqpad and then switch to view the SQL generated. That would provide an idea on how to structure one's SQL in a stored proc.

Also you could look into using SQL CTEs (Common Table Experssions) to build your query one step at a time until the data is just as needed.

Improve this answer

8 Comments

Thanks for the instruction, have previously tried in Stored procedure and views but due to over number of joins can't able to get the expecting result.thats why looking to fix in EF.
@Surendar I would build my linq query in Linqpad and then switch to view the SQL generated. That would give me an idea on how to structure my SQL in a stored proc. Also you could look into using SQL CTEs to build your query one step at a time until the data is just as needed. HTH
OmegaMan, This makes sense, will try this in Linqpad, thanks.
@Surendar If you do a .ToString() on a IQueryable<T> object generated from EntityFramework it also will give you the query that will be sent to the server if you would like to see the the query your LINQ statement is generating. You will need to replace the .FirstOrDefault() in your example query with the .ToString() for it to work.
-1 because both generated queries and stored procedures go to the query optimizer and get a query execution plan. That plan determines whether a table's index is used or not and in which order the tables are accessed and combined. The optimizer's choices are the primary performance consideration. "how the procedure has been structured with indexes" is completely incorrect.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.