Join gives correct result in SQL, but not (always) in LINQ
I have no idea whether I make a silly error here, or there is a problem with Linqpad or its SQLite driver. But the SQL version of a query gives the result I expect, whereas the LINQ version doesn't -- unless I make a change which should not (IMHO) change anything.
Let me please give the complete case, schema, data, and queries -- it's not that big.
Now I want to ask the question: Which attributes are in records which have an attribute of a certain type. That's the SQL query:
Given this sample data:
This is the result I get with LINQPad (or the sqlite3.exe command line):
Now, my best guess of the equivalent LINQ is:
But this gives only two rows:
Now, some tinkering around gave me this LINQ query, which IMHO should be equivalent to the previous one:
But that one gives six rows, the result set matching the SQL result set above.
What is going on here?
Interestingly the translated SQL for the first version of the LINQ query only shows one JOIN, completely dropping the second join -- and the fact that there are three tables present:
Let me please give the complete case, schema, data, and queries -- it's not that big.
CREATE TABLE RECORD (
RECORDID INTEGER NOT NULL PRIMARY KEY,
LABEL CHAR (40)
);
CREATE TABLE ATTR (
ATTRID INTEGER NOT NULL PRIMARY KEY,
TYPEID INTEGER NOT NULL,
REMARK CHAR (40),
RECORDID INTEGER NOT NULL
);
Now I want to ask the question: Which attributes are in records which have an attribute of a certain type. That's the SQL query:
select record.label, a1.remark, a2.typeid, a2.remark
from ((record
inner join attr as a1 on record.recordid = a1.recordid)
inner join attr as a2 on record.recordid = a2.recordid)
where a1.typeid = 100;
Given this sample data:
INSERT INTO RECORD VALUES (1, 'Alpha');
INSERT INTO RECORD VALUES (2, 'Beta');
INSERT INTO RECORD VALUES (3, 'Gamma');
INSERT INTO RECORD VALUES (4, 'Delta');
INSERT INTO ATTR VALUES (1, 100, 'Test', 1);
INSERT INTO ATTR VALUES (2, 200, 'Test.', 1);
INSERT INTO ATTR VALUES (3, 300, 'Test..', 1);
INSERT INTO ATTR VALUES (4, 200, 'Test...', 2);
INSERT INTO ATTR VALUES (5, 300, 'Test....', 2);
INSERT INTO ATTR VALUES (6, 400, 'Test.....', 2);
INSERT INTO ATTR VALUES (7, 300, 'Test......', 3);
INSERT INTO ATTR VALUES (8, 400, 'Test.......', 3);
INSERT INTO ATTR VALUES (9, 500, 'Test........', 3);
INSERT INTO ATTR VALUES (10, 400, 'Test.........', 4);
INSERT INTO ATTR VALUES (11, 500, 'Test..........', 4);
INSERT INTO ATTR VALUES (12, 100, 'Test...........', 4);
This is the result I get with LINQPad (or the sqlite3.exe command line):
Alpha Test 100 Test
Alpha Test 200 Test.
Alpha Test 300 Test..
Delta Test........... 100 Test...........
Delta Test........... 400 Test.........
Delta Test........... 500 Test..........
Now, my best guess of the equivalent LINQ is:
from r in RECORDs
join a1 in ATTRs on r.RECORDID equals a1.RECORDID
join a2 in ATTRs on r.RECORDID equals a2.RECORDID
where a1.TYPEID == 100
select new {label = r.LABEL, remark1 = a1.REMARK, typeid2 = a2.TYPEID, remark2 = a2.REMARK}
But this gives only two rows:
Alpha Test 100 Test
Delta Test........... 100 Test...........
Now, some tinkering around gave me this LINQ query, which IMHO should be equivalent to the previous one:
from r in RECORDs
join a1 in ATTRs on r.RECORDID equals a1.RECORDID
join a2 in ATTRs on a1.RECORDID equals a2.RECORDID
where a1.TYPEID == 100
select new {label = r.LABEL, remark1 = a1.REMARK, typeid2 = a2.TYPEID, remark2 = a2.REMARK}
But that one gives six rows, the result set matching the SQL result set above.
What is going on here?
Interestingly the translated SQL for the first version of the LINQ query only shows one JOIN, completely dropping the second join -- and the fact that there are three tables present:
SELECT t0.[LABEL], t1.[REMARK], t1.[TYPEID] AS [TYPEID1]
FROM [RECORD] AS t0
INNER JOIN [ATTR] AS t1
ON (t0.[RECORDID] = t1.[RECORDID])
WHERE (t1.[TYPEID] = 100)
Comments