Filter on nullable fields using DA Linq

Hello,

How can I query on the server on a nullable field? I’ve tried several things, but I always get an error that it is not supported. For example:

var lda = connection.LinqRemoteDataAdapter;
var query = from p in lda.GetTable<Products>()
    select p;
var products = query.Where(p => (p.Weight ?? 0) > 0).ToList();

where Weight is of type “double?” (nullable).

Thanks,
Arturo.

What’s the exact error? is it runtime or compile time? I assume this is Visual C#, or RemObjects C#?

Hello, Marc,

It’s a runtime error (VIsual C#, NET Standard libraries, v10.0.0.1457): “Coalesce is not supported as a binary operator”.

I tried other options as well, in addition to the coalesce operator, but I also get the “not supported” error.

Here is the error and call stack:

Thanks,
Arturo

Thanx. @antonk, any suggestions?

This should work:

var lda = connection.LinqRemoteDataAdapter;
var query = from p in lda.GetTable<Products>()
    select p;
var products = query.Where(p => p.Weight > 0).ToList();

It will be translated to SQL query like

SELECT ... FROM Products WHERE weight > 0

EDIT:
It is possible to write the query as

var products = query.Where(p => (p.Weight != null) && (p.Weight > 0)).ToList();

but there is no need to because p.Weight > 0 condition alone will do the same and will be faster db-wise.

Remember that this statement is not executed locally - it is just a way of describing some conditions that will be later converted into a SQL statement.
In SQL expression [Weight] > 0 will be True only when [Weight] is not NULL and [Value] is greater than NULL


Coalesce operator is not supported atm. I’ll log an issue to check if it is possible to add its support.

Thanks, logged as bugs://83409

ideally we should support == null and map it to IS NULL? since while the above might work for Weight, chances are there’ll be scenarios where we need to distinguish between NULL and 0…

1 Like

We do already.

F.e. this query

 var query = from x in dataAdapter.GetTable<dbo_NULLABLE_TEST>()
             where (x.VALUE > 0) || (x.VALUE != null)
             select x;

will be translated to database-level SQL

SELECT [t0].[KEY], [t0].[VALUE]
FROM [dbo].[NULLABLE_TEST] [t0]
WHERE (([t0].[VALUE] > 0) OR [t0].[VALUE] IS NOT NULL)

Similarly

 (x.VALUE != null)

would have been translated into

[t0].[VALUE] IS NULL

ok, then that should have bene the proper original answer?

You are right, the coalesce operator is not needed in this case. I’m not sure why I thought it was required to deal with the nullable and use the coalesce operator. Probably some version of the code gave me an error regarding the nullable, so I assumed this was always the case, but it’s not. Just tested this and it works fine with a simple p.Weight > 0 comparison.

Thanks, @antonk! This solves my problem.