Take the following Linq query

                    from c in db.Customers
                    join o in db.Orders on c.CustomerID equals o.CustomerID into og
                    from o in og.DefaultIfEmpty()
                    select new
                    {
                        c.CompanyName,
                        HasOrdered = (o.CustomerID != null) ? "Yes they have" : "No they havent"
                    }

When run, I get a 'Operator 'NotEqual' isn't supported.' error. Ok, so obviously I can make it == and flip the responses, but there are times where it might be needed. So, I tried the following:

                    from c in db.Customers
                    join o in db.Orders on c.CustomerID equals o.CustomerID into og
                    from o in og.DefaultIfEmpty()
                    select new
                    {
                        c.CompanyName,
                        HasOrdered = (!(o.CustomerID == null)) ? "Yes they have" : "No they havent"
                    }

and I get a Null reference exception, presumably because the order entity is null as it is the result of a left join, although it doesnt seem to get as far as executing any SQL.

so I try:

                    from c in db.Customers
                    join o in db.Orders on c.CustomerID equals o.CustomerID into og
                    from o in og.DefaultIfEmpty()
                    select new
                    {
                        c.CompanyName,
                        HasOrdered = (!(o == null) && !(o.CustomerID == null)) ? "Yes they have" : "No they havent"
                    }

which is slightly messy and may be completely the wrong way of going about it, but anyway this generates SQL, but it is broken.

exec sp_executesql N'
SELECT  [LPA_L1].[CompanyName], 
        CASE WHEN [].[LPFA_8] THEN 1 ELSE 0 END AS [LPFA_9], 
        @LO8c4924a51 AS [LPFA_10], 
        @LOf34a29412 AS [LPFA_11] 
FROM ( [Northwind].[dbo].[Customers] [LPA_L1]  
LEFT JOIN [Northwind].[dbo].[Orders] [LPA_L2]  
    ON [LPA_L1].[CustomerID] = [LPA_L2].[CustomerID])',
N'@LO8c4924a51 nvarchar(13),@LOf34a29412 nvarchar(14)',
@LO8c4924a51=N'Yes they have',
@LOf34a29412=N'No they havent'

with the [] causing it to die.

On another null related note, and maybe this should be in a different thread, but when returning non nullable types (guids, datetimes etc.) in 'select new {}' blocks where they could be null (ie as the result of a left join), Linq to LLBLGen returns the 'empty' defaults for those types (eg. Guid.Empty) whereas Linq to SQL converts them to nullable types, which is what I would (perhaps wrongly) expect.

Thanks again, and I hope at least some of it makes sense...

Will check it out, thanks for the info

About the null values in non-null fields: that can't be done in our framework, as the types themselves aren't nullable, or am I mistaken?

i.e.: ... select new { Foo = o.EmployeeId ... };

so when o.EmployeeId is NULL in the db, and 'Foo' isn't a nullable int but just an int, it won't be a nullable int at runtime of course.

All the other queries also work btw. So consider your issue solved in the next build

The conditional expression wasn't setting the flag that the boolean expressions should be considered as predicates. So they were seen as llblgen pro Expression objects (which also supports operators like '>' etc.) By setting this flag for conditional expressions the first query worked.

I'll upload a new build within an hour or so, so you can continue testing with the new code.

(edit) new build is now available.

Thanks for that, that fixed that up.

I have another question on this subject. In Linq to SQL I can do coalesce type operations like this:

                    from c in db.Customers
                    join o in db.Orders on c.CustomerID equals o.CustomerID into og
                    from o in og.DefaultIfEmpty()
                    select new
                    {
                        c.CompanyName,
                        HasOrdered = o.ShipRegion ?? ((o.CustomerID != null) ? "Yes they have" : "No they havent")
                    }

but in Linq to LLBLGen I get an 'Failed to convert parameter value from a ConditionalExpression to a String.' error, is this a bug or is this kind of operation not supported?

Also, when I try:

                    from c in db.Customers
                    join o in db.Orders on c.CustomerID equals o.CustomerID into og
                    from o in og.DefaultIfEmpty()
                    select new
                    {
                        c.CompanyName,
                        HasOrdered = (o.CustomerID != null) ? "Yes they have" : null
                    }

I get a 'Value cannot be null. Parameter name: key' error, again is this a bug or is it a result of LLBLGen's null handling as explained previously.

Many thanks again, your support is exemplary.

rossmcw wrote:

Thanks for that, that fixed that up.

I have another question on this subject. In Linq to SQL I can do coalesce type operations like this:

                    from c in db.Customers
                    join o in db.Orders on c.CustomerID equals o.CustomerID into og
                    from o in og.DefaultIfEmpty()
                    select new
                    {
                        c.CompanyName,
                        HasOrdered = o.ShipRegion ?? ((o.CustomerID != null) ? "Yes they have" : "No they havent")
                    }

but in Linq to LLBLGen I get an 'Failed to convert parameter value from a ConditionalExpression to a String.' error, is this a bug or is this kind of operation not supported?

It should be supported. The '??' statement creates a DbFunctionCall with COALESCE, but the parameter is still an unresolved function call to IIF() which should be converted into a CASE statement. So that's a bug in the way a '??' statement is handled.

Also, when I try:

                    from c in db.Customers
                    join o in db.Orders on c.CustomerID equals o.CustomerID into og
                    from o in og.DefaultIfEmpty()
                    select new
                    {
                        c.CompanyName,
                        HasOrdered = (o.CustomerID != null) ? "Yes they have" : null
                    }

I get a 'Value cannot be null. Parameter name: key' error, again is this a bug or is it a result of LLBLGen's null handling as explained previously.

Not sure what's causing this, but I'll check it out.

Many thanks again, your support is exemplary.

Well, keep on hammering the code, the more issues you and others report, the less bugs we'll have in our final product. The code should be pretty solid for average queries now, the issues found now are more in the combination of things, which go wrong here and there. We'll try to fix them a.s.a.p.

The first issue was caused by the fact that the SelectMany projection was handled but the handling result wasn't stored in the result expression (Preprocessor.cs -> HandleMethodCallSelectMany, line 986: set resultProjection to the result of HandleProjection. ).

This resulted in some dead code in queryexpressionbuilder which could be removed. The point was that originally, I didn't have an answer to the fact how to deal with boolean expressions in the projection. After I build that in, the whole route to llblgen pro expressions with boolean operators was useless, as a predicate can now be a parameter of a DbFunctioncall .

The second issue is resulting in an issue in DbFunctionCall inside the runtime lib. if you specify a value 'null' as parameter value, it gives an error (you ended up elsewhere in the code, after the fix of the first issue, your query dies inside the DbFunctionCall code).

Adding a simple check for null and changing it into DBNull.Value fixed that too (DbFunctionCall.cs -> line 222)