Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Entity Framework Core : LINQ advise needed on better approach using include for relational tables

I have a question about Entity Framework Core and using LINQ. I would like to get the other table details while accessing the Clients table. I can get them using below code. There are a total of around 10 tables I need to join, in this case is the below approach is good or any other, better approach? ClientId is the foreign key for all tables.

Actually I am getting a warning as below

[09:34:33 Warning] Microsoft.EntityFrameworkCore.Query Compiling a query which loads related collections for more than one collection navigation either via 'Include' or through projection but no 'QuerySplittingBehavior' has been configured. By default Entity Framework will use 'QuerySplittingBehavior.SingleQuery' which can potentially result in slow query performance. See https://go.microsoft.com/fwlink/?linkid=2134277 for more information. To identify the query that's triggering this warning call 'ConfigureWarnings(w => w.Throw(RelationalEventId.MultipleCollectionIncludeWarning))'

Code:

var client = await _context.Clients
                .Include(x => x.Address)
                .Include(x => x.Properties)
                .Include(x => x.ClientDetails)
                -------------------
                -------------------
                -------------------
                -------------------
                .Where(x => x.Enabled == activeOnly && x.Id == Id).FirstOrDefaultAsync();
like image 872
leo Avatar asked Apr 27 '21 01:04

leo


People also ask

Which is better Entity Framework or LINQ to SQL?

LINQ to SQL allow you to query and modify SQL Server database by using LINQ syntax. Entity framework is a great ORM shipped by Microsoft which allow you to query and modify RDBMS like SQL Server, Oracle, DB2 and MySQL etc. by using LINQ syntax. Today, EF is widely used by each and every .

What is difference between Entity Framework and LINQ?

Entity Framework is an object-relational mapping (ORM) framework for connecting C# code to external databases, usually SQL Server. LINQ is a query language embedded into C# and a set of extension methods in order to make it useful.

Can I use LINQ with Entity Framework?

Entity Framework Core uses Language-Integrated Query (LINQ) to query data from the database. LINQ allows you to use C# (or your . NET language of choice) to write strongly typed queries.

How to use LINQ to query against dbset in EF API?

Visit LINQ Tutorials to learn LINQ step by step. The DbSet class is derived from IQuerayable. So, we can use LINQ for querying against DbSet , which will be converted to an SQL query. EF API executes this SQL query to the underlying database, gets the flat result set, converts it into appropriate entity objects and returns it as a query result.

How to use LINQ include in Entity Framework?

LINQ Include we need to include the namespace System.Data.Entity in which the LINQ Include is an extension method of the Data.Entity namespace. Entity Framework version gives the provision of LINQ Include () by using EntityFramework.dll and System.Data.Entity.

How does efef work with LINQ?

EF API executes this SQL query to the underlying database, gets the flat result set, converts it into appropriate entity objects and returns it as a query result. The following are some of the standard query operators (or extension methods) that can be used with LINQ-to-Entities queries.

How to secure n+1 problem in Entity Framework?

In the above program, the Entity Framework tells that the Customer Details required their Invoices too. By using the LINQ Include method in the dbcontext it helps the lazy loading to secure the n+1 problem. Let’s understand the above example like how the LINQ Include functionality to load the related entities.


1 Answers

Actually when you use Eager loading (using include()) It uses left join (all needed queries in one query) to fetch data. Its default the ef behavior in ef 5. You can set AsSplitQuery() in your query for split all includes in separated queries. like:

var client = await _context.Clients
            .Include(x => x.Address)
            .Include(x => x.Properties)
            .Include(x => x.ClientDetails)
            -------------------
            -------------------
            -------------------
            -------------------
            .Where(x =>x.Id == Id).AsSplitQuery().FirstOrDefaultAsync()

This approach needs more database connection, but it's nothing really important. and for the final recommendation, I advise using AsNoTracking() for queries to high performance.

like image 189
Mujan Avatar answered Oct 30 '22 23:10

Mujan