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