BLToolKit generates wrong SQL

Question

I'm using BLToolKit as a ORM Mapper.

My problem is, that it generates the wrong SQL.

I have this query:

var qry = from i in s.Query<ChannelDTO>() 
            join o in s.Query<StorageShelfDTO>() on i.Id equals o.ChannelID into p1
            select new {i.Id, n = p1.Count()};

        var qry2 = qry;
        qry2 = qry2.Where(x => x.n == 0);
        Debug.Print("Entrys: " + qry2.ToList().ToString());

which generates this SQL:

SELECT
[x].[Id] as [Id1]  
FROM
(
SELECT
    (
        SELECT
            Count(*)
        FROM
            [WMS_StorageShelf] [t1]
        WHERE
            [i].[ID] = [t1].[ChannelID]
    ) as [c1],
    [i].[ID] as [Id]
FROM
    [WMS_Channel] [i]
) [x]
WHERE
[x].[c1] = 0

which is missing the count field in the outer select!

but when I remove the Condition:

 qry2 = qry2.Where(x => x.n == 0);

then the correct SQL is generated.

Answer 1

The generated query is correct.

You have your initial linq statement:

var qry = from i in s.Query<ChannelDTO>() 
            join o in s.Query<StorageShelfDTO>() on i.Id equals o.ChannelID into p1
            select new {i.Id, n = p1.Count()};

That will generate SQL similar to:

SELECT
    (
        SELECT
            Count(*)
        FROM
            [WMS_StorageShelf] [t1]
        WHERE
            [i].[ID] = [t1].[ChannelID]
    ) as [c1],
    [i].[ID] as [Id]
FROM
    [WMS_Channel] [i]

Now when you add the second part to your query (qry2) this will get added on to your original query because it is using deferred execution. Therefor the final query that gets made up looks like this when it is executed:

var finalQuery = (from i in s.Query<ChannelDTO>() 
                 join o in s.Query<StorageShelfDTO>() on i.Id equals o.ChannelID into p1
                 select new {i.Id, n = p1.Count()}).Where(x => x.n == 0)

And that's why you get the SQL generated the way it is.

If you don't want deferred execution and actually want two different queries then call .ToList() on your first query to force immediate execution and you will see the query you expect.

Like this:

var qry = (from i in s.Query<ChannelDTO>() 
            join o in s.Query<StorageShelfDTO>() on i.Id equals o.ChannelID into p1
            select new {i.Id, n = p1.Count()}).ToList();