Linq to SQL creates an additional sub select when connecting

I have a simple parent link that I would like to download from LINQ to SQL. I want to load the children at the same time as the parent. Generated SQL does too much work. He is trying to count the children and also join them. I will not update these objects. I will not add children to parents. I'm only interested in reading. I simplified the tables to a minimum. I actually have more columns. LINQ to SQL generates the following SQL

SELECT [t0].[parentId] AS [Id], [t0].[name], [t1].[childId] AS [Id2], 
[t1].[parentId], [t1].[name] AS [name2], 
( SELECT COUNT(*)
    FROM [dbo].[linqchild] AS [t2]
    WHERE [t2].[parentId] = [t0].[parentId]
) AS [value]
FROM [dbo].[linqparent] AS [t0]
LEFT OUTER JOIN [dbo].[linqchild] AS [t1] ON [t1].[parentId] = [t0].[parentId]
ORDER BY [t0].[parentId], [t1].[childId]

I do not know why it exists SELECT COUNT(*) .... I would prefer him to leave. Both the parent and child tables will have millions of rows in them. An additional request costs a lot of time. This seems unnecessary. Is there any way to make him leave? I also don't know where it comes from ORDER BY.

The classes are as follows.

[Table(Name = "dbo.linqparent")]
public class LinqParent
{
    [Column(Name = "parentId", AutoSync = AutoSync.OnInsert, IsPrimaryKey = true, IsDbGenerated = true, CanBeNull = false)]
    public long Id { get; set; }

    [ Column( Name = "name", CanBeNull = false ) ]
    public string name { get; set; }

    [Association(OtherKey = "parentId", ThisKey = "Id", IsForeignKey = true)]
    public IEnumerable<LinqChild> Kids { get; set; }
}


[Table(Name = "dbo.linqchild")]
public class LinqChild
{
    [Column(Name = "childId", AutoSync = AutoSync.OnInsert, IsPrimaryKey = true, IsDbGenerated = true, CanBeNull = false)]
    public long Id { get; set; }

    [ Column( Name = "parentId", CanBeNull = false ) ]
    public long parentId { get; set; }

    [Column(Name = "name", CanBeNull = false)]
    public string name { get; set; }
}

I use something like the following for the query: there will be a where clause and its index.

using (DataContext context = new DataContext(new DatabaseStringFinder().ConnectionString, new AttributeMappingSource()) { ObjectTrackingEnabled = false, DeferredLoadingEnabled = false })
{
    var loadOptions = new DataLoadOptions();
    loadOptions.LoadWith<LinqParent>(f => f.Kids);
    context.LoadOptions = loadOptions;
    var table = context.GetTable<LinqParent>();
    context.Log = Console.Out;

    // do something with table.
}
+3
source share
2 answers

Unfortunately no. ORMs are never the most effective solution; you will always get better performance if you write your own SQL (or use stored procedures), but do a trade-off.

, , - ORM; , ( , ORM), ORM - , , - .

ORDER BY, , .

0

. , , (*) . order by , , , , .

, , LINQ , context.Log . Console.Out .

, .

Edit:

, , (*) , - 33% , , sql. , proc .

0

All Articles