How to write SQL IsNull() in Linq?

Question

Possible Duplicate:
Equivalent of SQL ISNULL in LINQ?

I am recently migrated from ADO.Net to Entity Framework

I have problem in executing this query in Linq

select IsNull(MAX(InvoiceNo),0) from Sales

I have written this query in LINQ except isNull()

        var q = (from p in dbContext.Sales
                 select p.InvoiceNo).Max();

But i dont know how to use IsNull() in this Linq

When i execute this Linq Query I am getting this exception.

'The invocation of the constructor on type 'JIMS.ViewModel.Transaction.SalesViewModel' that matches the specified binding constraints threw an exception.' Line number '8' and line position '6'.

I am using WPF MVVM FrameWork with Entity FrameWork as DAL

Magnus · Accepted Answer · 2012-03-20T16:53:38.420

9

var InvoiceNo = dbContext.Sales.Max(x => (int?)x.InvoiceNo) ?? 0;

edited Mar 20 '12 at 16:53

answered Mar 20 '12 at 16:38

Magnus

45,362
8
80
118

Is this also Linq or Anonymous Method – Kishore Kumar Mar 20 '12 at 16:45
but `??` works only on nullable int `int?` so its throwing an Exception – Kishore Kumar Mar 20 '12 at 16:50
Just cast it to a nullable object (int?)x.InvoiceNo if the underlying type is not nullable – Magnus Mar 20 '12 at 16:53
which is faster Lambda Express or Linq – Kishore Kumar Mar 20 '12 at 16:56
Both is linq actually, one is query syntax and the other lambda syntax. The performance is the same. – Magnus Mar 20 '12 at 17:00

score 3 · Answer 2 · answered Mar 20 '12 at 16:27

3

Try this:

var q = (from p in dbContext.Sales
                 select (int?)p.InvoiceNo).Max();

answered Mar 20 '12 at 16:27

Kyberias

1,263
1
14
23

score 0 · Answer 3 · answered Mar 20 '12 at 16:35

You will have to selectzero if invoicno is null. One way you could do this would be:

var elements = (from p in dbContext.Sales
                 where p.InvoiceNo != null
                 select p.InvoiceNo).Max();

Assuming there'll always be at least one invoiceno if not you can do

var elements = (from p in dbContext.Sales
                 select p.InvoiceNo ?? 0).Max();

which will have to compare all elements so it could be slower than the first approach

How to write SQL IsNull() in Linq?

3 Answers3