How to use a sql function (coalesce or isnull) in Simple.Data

Question

I am porting a sql query to Simple.Data, the original query is something like:

select a.Field1, a.Field2, b.Field1
from TableA a
join TableB b ON a.KeyField = b.KeyField
where coalesce(b.SomeDate, '1/1/1900') <= getdate()

I've been able to port everything in the query except for that darn coalesce logic:

var currentDate = DateTime.Now;
var result = db.TableA.Query()
    .Join(db.TableB).On(db.TableA.KeyField == db.TableB.KeyField &&
        db.TableB.SomeDate == currentDate)
    .Select(db.TableA.Field1, db.TableA.Field2, db.TableB.Field1);

Any thoughts on how to get the coalesce behavior in there? I've tried using the ?? operator to no avail.

Thanks in advance!

Mark Rendle · Accepted Answer · 2012-02-28T10:35:41.440

When there is a SQL function which takes a column name as its first parameter, you can specify it as a method on the column:

var currentDate = DateTime.Now;
var defaultDate = new DateTime(1900,1,1);
var result = db.TableA.Query()
    .Join(db.TableB).On(db.TableA.KeyField == db.TableB.KeyField &&
        db.TableB.SomeDate.Coalesce(defaultDate) <= currentDate)
    .Select(db.TableA.Field1, db.TableA.Field2, db.TableB.Field1);

Note that if you have referential integrity set up between TableA and TableB, you can leave out the explicit join and simplify this query to:

var currentDate = DateTime.Now;
var defaultDate = new DateTime(1900,1,1);
var result = db.TableA
    .Query(db.TableA.TableB.SomeDate.Coalesce(defaultDate) <= currentDate)
    .Select(db.TableA.Field1, db.TableA.Field2, db.TableB.Field1);

@MarkRendle how to use cast function for eg. cast(col_abc as integer) — Pavan Jaju, Dec 27 '14 at 11:10
@PavanJaju Not possible, unfortunately. I'll look into it for v2. — Mark Rendle, Jan 05 '15 at 10:03

Cᴏʀʏ · Answer 2 · 2012-02-23T23:38:44.937

I've never used Simple.Data before, but I think you want to do this:

var currentDate = DateTime.Now;
var nullDate = new DateTime(1900, 1, 1);
var result = db.TableA.Query()
    .Join(db.TableB).On(db.TableA.KeyField == db.TableB.KeyField &&
        ((!Object.ReferenceEquals(null, db.TableB.SomeDate) ? db.TableB.SomeDate : nullDate) <= currentDate)
    .Select(db.TableA.Field1, db.TableA.Field2, db.TableB.Field1);

Or move it out of the join:

var currentDate = DateTime.Now;
var nullDate = new DateTime(1900, 1, 1);
var result = db.TableA.Query()
    .Join(db.TableB).On(db.TableA.KeyField == db.TableB.KeyField)
    .Where((!Object.ReferenceEquals(null, db.TableB.SomeDate) ? db.TableB.SomeDate : nullDate) <= currentDate)
    .Select(db.TableA.Field1, db.TableA.Field2, db.TableB.Field1);

What is the data type for db.TableB.SomeDate? If Simple.Data generates it as a nullable data type (Nullable<DateTime>) then you could do:

db.TableB.SomeDate.GetValueOrDefault(nullDate) <= currentDate

Hi Cory. Simple.Data is an ORM that uses dynamic typing, so none of the code starting with `db.TableA` has any compile-time enforcement, and instead is interpreted by the Simple.Data API in order to translate the c# stuff into SQL. Hence the problem. If I were using "straight c#", either of your solutions would work fine. More info on Simple.Data [here](https://github.com/markrendle/Simple.Data/wiki) — EricP, Feb 23 '12 at 23:24
@EricP: I did a little research and updated the answer. Underneath the hood, `dynamic` objects are still just objects. You should be able to compare them to `null`. See above. — Cᴏʀʏ, Feb 23 '12 at 23:37

score 0 · Answer 3 · answered Feb 23 '12 at 21:10

0

Coalesce is quite useless here.

where coalesce(b.SomeDate, '1/1/1900') <= getdate()

this essentially means that if b.SomeDate is null then return true since getdate() would never be <= 1/1/1900. You can replace the above with this....

where (b.SomeDate is null) or (b.SomeDate <= getdate())

this might simplify it a little...

answered Feb 23 '12 at 21:10

sam yi

4,806
1
29
40

Hi Sam, your strategy works in this case, though it still doesn't solve the underlying problem of how to use a sql function from simple.data. Is there a way to award partial credit? :p – EricP Feb 23 '12 at 23:34

How to use a sql function (coalesce or isnull) in Simple.Data

3 Answers3