LINQ to LightSpeed query not populating IEnumerable, despite getting correct results from the database

Hi, I am currently working on a LINQ query against a LightSpeed model that is giving me a headache. After boiling down the query to its most essential form, I believe that I might just have hit a bug in LightSpeed.

Consider the following database schema:

Table: Articles --> Table: ArticleSubjects <-- Table: Subjects

ArticleSubjects is a many-to-many relation between articles and subjects. It has been created with the model-first-approach, and the foreign key relations (which compose the primary key) have been set up as outlined in this article: http://www.mindscapehq.com/blog/index.php/2010/06/17/many-to-many-associations-and-composite-keys-in-lightspeed/

The database is based on Oracle 10g, using the "Oracle9" provider in the LightSpeed model.

Basically, the following three LINQ queries should return identical results:

1) IEnumerable data1 = uow.ArticleSubjects.Where(x => x.Id.Articleid == 1326).ToList();

2) IEnumerable data2 = uow.ArticleSubjects.Where(x => x.Article!=null && x.Article.Id == 1326).ToList();
//(the "x.Article!=null" is necessary to prevent a null reference exception in LightSpeed, as outlined in this forum post: http://www.mindscapehq.com/forums/Thread.aspx?PostID=9182)

3) IEnumerable data3 = uow.Articles.Where(x => 1326==x.Id).Select(x => x.ArticleSubjects).ToList();

Looking at the generated SQL code with the trace logger yields that the SQL query that is run against the database is identical in all three cases (the 2nd query changes the WHERE clause to "(ARTICLESUBJECTS.Articleid IS NOT NULL AND ARTICLESUBJECTS.Articleid = 1326)", which is irrelevant for the given problem:

SELECT
  ARTICLESUBJECTS.Articleid AS "articlei_0",
  ARTICLESUBJECTS.Subjectid AS "subjecti_0"
FROM
  ARTICLESUBJECTS
WHERE
  ARTICLESUBJECTS.Articleid = 1326;

If I run that query in SQL*Plus, it correctly returns 9 results. It also corrently returns 9 results in the IEnumerable for in the LINQ queries 1) and 2). However, the IEnumerable in LINQ query 3) is always empty. The results, that are correctly returned from the database, get lost somewhere on the way.

This behaviour has been tested and reproduced with LightSpeed 3.11, the latest 3.x nightly build and LightSpeed 4.0.

The fact that LINQ query 2) succeeds lets me believe that the foreign key relation is correctly set up. Is this type of query (Selection of related entity collections) supported in LightSpeed? Am I missing something?

Thanks,
Chris

Hi Chris,

I think there is something slightly amiss with query 3, the one that is giving problems.  Articles.Where(...).Select(a => a.ArticleSubjects.ToList()) will I think give you a List<EntityCollection<ArticleSubject>>, where the List would contain one EntityCollection.  I am a bit surprised that LightSpeed is trying to handle this at all!  I think what you need is SelectMany, to flatten the (one-element) List of collections into a single list:

Articles.Where(...).SelectMany(a => a.ArticleSubjects).ToList();

I am not sure if this will fix the problem but please give it a try and let us know if you're still seeing incorrect results.  Thanks!

Thanks, that was it!

Slightly related: Do you have any idea why I need the "x.Article!=null" in the 2nd query? The linked forum post just ended with "Cannot reproduce", but the error seemingly still exists.

Thanks,
Chris

We're not sure why you would need this as we haven't been able to reproduce the error.  If you're able to provide us with a minimal model and an NUnit test or console application that exhibits the error, we'd be pleased to look into it.

There you go.

It's a simple WinForms app, built against a local SQL Express database (a SQL script to create the DB and sample values is part of the solution), using LightSpeed 4.0.

The "Produce Result" button executes the query with the "Entity!=null" fix, the "Produce Error" button without.

Cheers,
Chris

Thanks, Chris, that was exactly what we needed.  The crucial detail was that the FK for the Subject association is part of the PK rather than an independent field.  It should be fixed in the next nightly build.