Unexpected result using Linq SingleOrDefault()

A simple one to many relation, Table foo contains 1 entry (with name "TargetFoo") Table bar contains 2 entries linked to the entry in foo

I have this query to join and map the result to a DTO

var query = from foo in _work.Foo
                        join bar in _work.Bar
                            on foo.Id equals bar.FooId into foobar
                        select new foobarDTO()
                            {
                                Id = foo.Id,
                                Name = foo.Name,
                                Bars = new BarList(foobar.Select(x => x.Name))
                            };

When I run

var actual = query.SingleOrDefault(x => x.Name == "TargetFoo"); 

I get a "Sequence contains more than one element" exception

   at Mindscape.LightSpeed.Linq.Plan.LimitToSingleResult.ApplyTo(Object interimResult, Type returnType)
   at Mindscape.LightSpeed.Linq.Plan.SingleQueryPlan.PostProcessRawQueryResult(Object rawResult)
   at Mindscape.LightSpeed.Linq.Plan.SingleQueryPlan.ExecuteImmediate(IUnitOfWork unitOfWork, Type returnType)
   at Mindscape.LightSpeed.Linq.LinqQueryProvider.Execute(Expression expression)
   at Mindscape.LightSpeed.Linq.LinqQueryProvider.System.Linq.IQueryProvider.Execute[S](Expression expression)
   at System.Linq.Queryable.SingleOrDefault[TSource](IQueryable`1 source, Expression`1 predicate)

If I instead do a

var actual = query.ToList().SingleOrDefault(x => x.Name == "TargetFoo");

I do get the expeted result, which is a mapped foobarDTO.

I'm doing this against a MySql database and the SQL generated is:

SELECT
  t0.Id AS "t0.Id",
  t0.Name AS "t0.Name",
  t0.UpdatedOn AS "t0.UpdatedOn",
  t1.Id AS "t1.Id",
  t1.FooId AS "t1.FooId",
  t1.Name AS "t1.Name"
FROM
  Foo t0
LEFT OUTER JOIN
  Bar t1
ON
  t0.Id = t1.FooId

Is this intended behaivour?

Yes this sounds correct since you will have 2 rows returned from the query - e.g. from executing the SQL statement above - is that correct?

Jeremy

Yes, the SQL query will return two (or more) rows, but the Select statement in the Linq expression is written to unflatten/treeify the result from the database.

In this case I would have expected the SingleOrDefault() to be applied to the projection (the result of the select new foobarDTO), not to the SQL query itself. And the deep stacktrace led me to consider that it could be an issue with LightSpeed.

The query is implemented in a repository layer that lies between the service layer and the lightspeed entities. It is basically using Linq to map IQueryable to a IQueryable for the service layer.

To get the expected result I can use this query in the servicelayer, but it would be prefered if the servicelayer wasn't forced to enumerate the query before applying more Linq

var actual = repo.Query().Where(x => x.Name== "TargetFoo").Take(2).ToList().SingleOrDefault();

The goal is to determine if there is at zero or one Foo, regardless how many Bars are associated with it.

Edit: Actually, that query will not be correct either as the limit could hide Foos from the result if another Foo has two or more Bars, so the only course of action would be to force an execute the query and do SingleOrDefault() on the result. So the question is if this type of operation is supported in LightSpeed?

I've been at this all day since this feature is (or maybe was) critical functionality.

The problem actually goes beyond the SingleOrDefault(). I had hoped that any skip/take added to the linq query after the projection would be based on the projection. This does not seem to be the case and is applied on the base query.

So if you have a single Foo with 5 Bars, a take(1) would actually only include one of the Bars in the final projection.

What I'm trying to do appears to be a Group Join as per http://msdn.microsoft.com/en-us/library/bb311040.aspx

Basically I had hoped that the SQL ended up something like this

SELECT
  t0.Id AS "t0.Id",
  t0.Name AS "t0.Name",
  t1.Id AS "t1.Id",
  t1.Name AS "t1.Name"
FROM
  (SELECT
    Foo.Id,
    Foo.Name
  FROM
    Foo
  LIMIT 1 
  ) t0
LEFT OUTER JOIN
  Bar t1
ON
  t0.Id = t1.FooId;

Note the Limit on the subquery, this is the Take(1) I would like to append to the query after the projection.

Any suggestions on another way to implement this? We can't be the first ones to try and use Linq-only mapping from an IQueryable of Entity to IQueryable of DTO and then apply pagination elsewhere.

Reference:

Test cases: http://pastebin.com/sWA5pKkC

The model used: http://pastebin.com/ydXSNV6r

The background to this is when handling enumeration of an IQueryable we have to translate the LINQ expression we receive into a LightSpeed query but there is a mismatch between what LINQ can express and what can be expressed with our query objects. Additionally there is often a mix of server side and client side operations, we are primarily expecting there to be (and to translate to) server side operations since that is what a LightSpeed query can express.

One issue you raised above is with Take occurring on the base query rather than on the projection. Certainly the way your query is written your intent is for it to occur after the projection (which ultimately ends up being a client side operation) however when translating the query we apply operations to the underlying query object(s) so any concerns which are intended to be handled via LINQ to Objects (such as the paging concern in this example) will be mistranslated.

As a general rule you dont want to mix server side and client side concerns. While we do our best to translate this correctly in some cases this is incorrect (e.g. the above query, or more generally with client side operations intended to occur what will end up having to be a client side projection). To provide some "hints" though you can force enumeration which clearly delineates the server and client side aspects in the query. Ultimately this will occur anyway so there is no additional cost here but it means that we dont accidentally translate something like the paging concern which should have ultimately ended up as a client side operation.

e.g. From your attached query, adding .ToList before the Take call will force the paging concern client side.

return
            (from foo in foos
            join bar in bars on foo.Id equals bar.FooId into foobar
            let barlist = foobar.OrderBy(x => x.Name).Select(x => x.Name)
            select new FooBarDTO()
                        {
                            Id = foo.Id,
                            Name = foo.Name,
                            Bars = barlist
                        }).ToList().Take(1);

So that is one approach.

For finer control of the queries themselves you can work with the underlying querying directly however for situations above where you want to project our the same named columns from multiple tables you will need to write your own code to handle the mapping.

But ignoring that side of it you could write something like this:

 var outer = new Query(typeof(Bar));

 outer.Mappings.Add(typeof(Bar), "t0");
 outer.Mappings.Add(typeof(Foo), "t1");

 var inner = new Query(typeof(Foo)) { Page = Page.Limit(1) };

 outer.Join = Join.Inner(typeof(Bar), inner, "FooId", "Id");

 outer.Projection.Add("t0", "Id");
 outer.Projection.Add("t0", "Name");
 outer.Projection.Add("t1", "Id");
 outer.Projection.Add("t1", "Name");

 var rawResults = unitOfWork.Project(outer);

Assuming you were able to then map these into an object structure you could then restructure those raw result into the FooBarDTO by doing something like:

 objectSet.GroupBy(o => o.Id)
      .Select(g => new FooBarDTO() { Id = g.Key, Name = g.First().Name, Bars = g.Select(b => b.BarNameProperty) })

Jeremy

Thank you for that extensive reply. I guess that the downside of relying on Linq is that it tends to make things too easy and that you're not fully aware what goes on behind the scenes.

In this specific case I think it will be fine do do the paging on the client side as the result fetched from the database will be quite limited, and the query one of the lesser used ones. However, for frequently accessed tables with millions of rows in the database, client side paging isn't really an option. Those DTOs will probably not require any joins though, just a straight up projection mapping. That would mean that the limit would be applied directly and correctly.

To be honest I was pleasantly surprised to notice that linq orderby() and where() were mapped beyond the projection and applied to the SQL query. Even when joining in a many-to-one scenatio.

We'll be sure to fully cover any Linq queries in integration testing to avoid further surprises. Speaking of which, are you guys interested in knowing about any issues we find, where the behaivour of a ListBased IQueryable differs from the IQueryables from the UnitOfWorks? I'm not talking about explicit "this is not supported" exceptions, but other exceptions with deeper LightSpeed stacktraces or other inconcistencies.

Now, I just have to teach ReSharper that it should not replace query.Where(x => x.name == "name").Count() with query.Count(x => x.name == "name")

Thanks again for the help.

Also, yay for half day timezone difference =)

No problem and yes we are certainly interested in any potential issues you find so please send them through (ideally with a repro case please!) :)

Thanks!
Jeremy