Whats wrong with this LINQ query

Question

var item1 = (from a in db.Item
                         where
                         (a.ItemNo == Item.ItemNo ||  Item.ItemNo==null ) &&
                         (a.StatusId == Item.StatusId || Item.StatusId == 0) &&
                           (a.LocationId == Item.LocationId || Item.LocationId == 0)
                         select a).ToList();

            return View(item1);

I am getting exception

[ Argument # = 1,Name of function(if known) = isnull ]

I have also tried this code

 var item1 = (from a in db.Item
                         where
                         (a.ItemNo == Item.ItemNo ||  Item.ItemNo=="") &&
                         (a.StatusId == Item.StatusId || Item.StatusId == 0) &&
                           (a.LocationId == Item.LocationId || Item.LocationId == 0)
                         select a).ToList();

            return View(item1);

But Not working getting Error

[String truncation: max=0, len=2, value='10'. ]

i am trying but getting conversion error i understood ur logic Means If String is empty Pass default value else Pass value.. but getting conversion error IS "There is no implicit conversion between string and bool" — Kaps Hasija, Jul 09 '12 at 06:45
hey just one change in edited answer you need to put a.ItemNo==a.ItemNo rather than a.ItemNo only , you can check in my answer... — Pranay Rana, Jul 09 '12 at 07:16
[[ Argument # = 1,Name of function(if known) = isnull ]] getting this error again while using urs statement — Kaps Hasija, Jul 09 '12 at 07:32

score 1 · Accepted Answer · edited May 23 '17 at 11:56

1

You could try writing it as a Lamdba expression, see if it is the same issue?

var item1 = db.Item.Where(a => (String.IsNullOrEmpty(Item.ItemNo) || a.ItemNo == Item.ItemNo) &&
                               (Item.StatusId == 0 || a.StatusId == Item.StatusId) &&
                               (Item.LocationId == 0 || a.LocationId == Item.LocationId)).FirstOrDefault();

return View(item1);

Update

Appears EF doesn't support String.IsNullOrEmpty see this question. You can re-write your query like:

var item1 = db.Item.Where(a => ((Item.ItemNo == null || Item.ItemNo == String.Empty) || a.ItemNo == Item.ItemNo) &&
                               (Item.StatusId == 0 || a.StatusId == Item.StatusId) &&
                               (Item.LocationId == 0 || a.LocationId == Item.LocationId))
                   .FirstOrDefault();

If you are still having trouble checking for null, then you could break the query up e.g.

var query = db.Item.Where(a => (Item.StatusId == 0 || a.StatusId == Item.StatusId) &&
                               (Item.LocationId == 0 || a.LocationId == Item.LocationId));

if (!String.IsNullOrEmpty(Item.ItemNo))
{
    query = query.Where(a => a.ItemNo == Item.ItemNo);
}

var item1 = query.FirstOrDefault();

edited May 23 '17 at 11:56

Community

1
1

answered Jul 09 '12 at 09:43

James

80,725
18
167
237

i am getting same error [ Argument # = 1,Name of function(if known) = isnull ] – Kaps Hasija Jul 11 '12 at 04:29
@KapsHasija EF doesn't support `String.IsNullOrEmpty` which is why you are seeing that particular exception. See my updated post. – James Jul 11 '12 at 07:49
@KapsHasija what type is `ItemNo`? – James Jul 11 '12 at 10:47
Why don't you test each individual condition separately to see which one is failing (may not necessarily be the `ItemNo` one). – James Jul 12 '12 at 07:48
i have tested to comment my itemno. code , problam only occurred with Itemno. Thanks waiting for reply badly – Kaps Hasija Jul 13 '12 at 04:16
@KapsHasija I take it if you remove the check for `null` completely it works ok? There are no known issues I can think of with checking for null with EF. – James Jul 13 '12 at 07:39
hi james i found main error actually thing is that when i used my query with web grid or normal lay out ..it works well but when i used jquery grid it not works.. have you any idea – Kaps Hasija Jul 16 '12 at 12:57
@KapsHasija Sorry never used jQuery Grid before, you might be better either re-posting a new question focusing it towards jQuery grid users or post it on their forums. – James Jul 16 '12 at 13:17
@KapsHasija Check the update to my answer. It might solve your issues. – James Jul 17 '12 at 08:33

Pranay Rana · Answer 2 · 2012-07-09T07:13:50.733

EDIT Try soemthing like this

 string.IsNullOrEmpty(Item.ItemNo) ? a.ItemNo==a.ItemNo
                  : a.ItemNo == Item.ItemNo;

code for you is

var item1 = (from a in db.Item 
                    where
                    ( string.IsNullOrEmpty(Item.ItemNo) ? a.ItemNo==a.ItemNo
                  : a.ItemNo == Item.ItemNo; ) &&
                     ..more

Note if the value of Item.ItemNo is null or empty than you should do a.ItemNo==a.ItemNo sot hat it satisfy your condition...

Make sure that Your itmeno field is nullable as like below

int? ItemNo { get; set; }

and tryout soemthing like this

Item.ItemNo == null ? a.ItemNo : a.ItemNo == Item.ItemNo

so it will ve

var item1 = (from a in db.Item 
                    where
                    ( Item.ItemNo == null ? a.ItemNo : a.ItemNo == Item.ItemNo ) &&
                     ..more

i am getting same error [ Argument # = 1,Name of function(if known) = isnull ] — Kaps Hasija, Jul 11 '12 at 04:29

Whats wrong with this LINQ query

2 Answers2