SelectMany LINQ

Just thought I would add this to the LINQ Requests. Apparently SelectMany().Count() does not use the current filter.

I have this as part of a UnitTest

var stock = from t in work.MaterialDefs
                        where t.type == "W" && t.size == "8x67" && t.grade == "A992"
                        select t;
            var count = stock.Count();
            Assert.AreEqual(2, count);//Passes

            var mat = stock.SelectMany(s => s.AllMaterials);
            count = mat.Count();
            Assert.AreEqual(5,count);//Fails count is 2054

-------------------------------------------------------------------------------

Here is the SQL Produced

SELECT
  COUNT(*)
FROM
  MaterialDef
WHERE
  (((MaterialDef.Type = 'W' AND MaterialDef.Size = '8x67') AND MaterialDef.Grade = 'A992') AND MaterialDef.DeletedOn IS NULL)

--> Time:   4 ms


SELECT
  COUNT(*)
FROM
  Material
WHERE
  (Material.OriginalMaterialDefId IS NOT NULL AND Material.DeletedOn IS NULL)

What Happened to the filter from the First Query?? It should be applied to the second query as an Exists Statement

Thanks for alerting us to this.  We have implemented only limited SelectMany support in LightSpeed 2, and compound where clauses are not supported.  Unfortunately this will be fairly difficult for us to fix at the moment.  Is it viable for you to perform this client-side, i.e.

var mat = stock.ToList().SelectMany(s => s.AllMaterials);

Obviously the downside of this is that it will load the results of the stock query, when all you want is a count, but depending on how many results you expect this may not be too big an issue -- let us know.

We will of course add your scenario to our test base for the LightSpeed 3 LINQ provider.

 I can perform this client side. Just thought I would let you know and give examples so when SelectMany is ready you have some tests.