Problem with related data
I've created a new SQL Server 2000 database called LINQTestDB, and within it created two tables:
Parent
IDint (identity) PK
Descriptionnvarchar(255)
Children
IDint (identity) PK
ParentIDint
Descriptionnvarchar(255)
I've populated them with data as follows:
Parent
ID Description
1 Parent1
2 Parent2
3 Parent3
4 Parent4
ID ParentID Description
1 1 P1_C1
2 1 P1_C2
3 2 P2_C1
4 2 P2_C2
5 2 P2_C3
6 3 P3_C1
7 3 P3_C2
8 4 P4_C1
9 4 P4_C2
I've defined a relationship between the two tables, linking Parent.ID (PK) to Children.ParentID (FK).
I've used the Sqlmetal application to generate C# objects from my database:
SqlMetal.exe /server:localhost /database:LINQTestDB /code:LINQTestDB.cs
I've created a new LINQ C# Console application, added the LINQTestDB.cs file generated by Sqlmetal, and the ObjectDumper.cs file included in the LINQ samples.
My application code is as follows:
using System; using System.Collections.Generic; using System.Text; using System.Query; using System.Xml.XLinq; using System.Data.DLinq; namespace LINQTest { class Program { staticvoid Main(string[] args) { LINQTestDB db =new LINQTestDB("Data Source=localhost;Initial Catalog=LINQTestDB;User Id=sa;Password=******;"); db.Log = Console.Out; Table<Parent> parents = db.GetTable<Parent>(); var q = from pin parents select p; ObjectDumper.Write( q, 1 ); Console.Read(); } } } |
As far as I can tell thisshoulddisplay each record fromParent, and each of the related records fromChildren. The output I get is as follows:
SELECT [t0].[ID], [t0].[Description]
FROM [Parent] AS [t0]
ID=1 Description=Parent1 Children=...
SELECT [t0].[Description], [t0].[ID], [t0].[ParentID]
FROM [Children] AS [t0]
WHERE [t0].[ParentID] = 1
Children: ID=1 ParentID=1 Description=P1_C1 Parent={ }
Children: ID=2 ParentID=1 Description=P1_C2 Parent={ }
ID=2 Description=Parent2 Children=...
Children: ID=2 ParentID=1 Description=P1_C2 Parent={ }
ID=3 Description=Parent3 Children=...
SELECT [t0].[Description], [t0].[ID], [t0].[ParentID]
FROM [Children] AS [t0]
WHERE [t0].[ParentID] = 3
Children: ID=6 ParentID=3 Description=P3_C1 Parent={ }
Children: ID=7 ParentID=3 Description=P3_C2 Parent={ }
ID=4 Description=Parent4 Children=...
SELECT [t0].[Description], [t0].[ID], [t0].[ParentID]
FROM [Children] AS [t0]
WHERE [t0].[ParentID] = 4
Children: ID=8 ParentID=4 Description=P4_C1 Parent={ }
Children: ID=9 ParentID=4 Description=P4_C2 Parent={ }
The output is correct forParent ID's 1, 3 & 4. However for ID 2, it displays incorrect related data. The SQL logging reveals that the database is never queried forChildren whose ParentID = 2. It seems to skip this step. I get the same result if generate the output using foreach statements instead of the ObjectDumper.
If I change some of the ParentID's in the Children table, I get varying incorrect results (e.g. ParentID's 1 & 2 will be correct, but 3 & 4 will fail to query the DB and therefore show incorrect data).
I'm completely stumped at this point, so if anyone can find find a problem in my code that could possibly cause this error, I'd be very happy, but I'm starting to think that its caused by a problem in DLinq.
