.NET Core / EF : how to avoid performance issue with Linq query?-CodePudding

I have a few questions on lambda filtering performance.

This is my code:

public class supplier 
{
    public Guid supplier_id { get; set; }
    public string supplier_name { get; set; }
    public Guid township_id { get; set; }
}

public class township
{
    public Guid township_id { get; set; }
    public string township { get; set; }
}

This is my dto class:

public class supplier_dto 
{
    public Guid supplier_id { get; set; }
    public string suppliername { get; set; }
    public Township township { get; set; }
}

So why I filter for list using Linq:

var supplier_list = (from sup on _dbcontext.suppliers
                     join twn on _dbcontext.townships on sup.township_id equals twn.township_id
                     select new supplier_dto  
                                {
                                    supplier_id = sup.supplier_id,
                                    suppliername = sup.supplier_name,
                                    township = getTownshipById(twn.township_id)
                                }).ToList();

If there are many suppliers, will this affect the performance? Is there a way to do without using getTownshipById function?

BR, jm

CodePudding user response：

There are several issues with your classes. First, I assume you have one-to-many relationship between township and suppliers, which is typically represented by foreign key in the database. Therefore you have to have similar relationship in the model:

public class supplier 
{
    public Guid supplier_id { get; set; }
    public string supplier_name { get; set; }
    public Guid TownshipId { get; set; }
    public Township township { get; set; }
}

public class Township
{
    public Guid TownshipId { get; set; }
    public string name { get; set; }
    public ICollection<supplier> suppliers { get; set; }
}

If that is the case, you can rewrite you LINQ statement:

var supplier_list = (from sup in _dbcontext.suppliers
                     select new supplier_dto  
                                {
                                    supplier_id = sup.supplier_id,
                                    suppliername = sup.supplier_name,
                                    township = sup.township
                                }).ToList();

Just make sure that township is actually loaded either through eager or explicit loading. In general, I consider it code smell if you need to use join in LINQ statement.

Second, the performance of LINQ query depends on the query that it generates. You put a label of .NET 3.1. In .NET 5 you can use an easy API ToQueryString() to get the resultant query; but even in 3.1 you can generate it with a little more effort.

Finally, your question doesn't have anything to do with lambda