Linq to Sql - return all data if 'contains' value is null in where clause

Question

I need to get all vendorname if value(data.vendorname) is null its give an error Exception: "Value cannot be null."

public HttpResponseMessage PostFilter([FromBody] dynamic data)
{
    string[] vendorname = data.vendorname != null
                          ? data.vendorname.ToObject<string[]>()
                          : null;

    var items = (from s in context.AllInventories
                 where
                    (vendorname!=null
                     ? vendorname.Contains(s.VENDORNAME)
                     :1==1)
                 select s)
                 .Take(500)
                 .ToList();
}

You can improve the elegance of your null checks with the C#6 null-propagator: `string[] vendorname = data?.vendorname?.ToObject();` — , Feb 05 '16 at 11:39
@romanoza at select query its give an exception . whenever vendorname is null its give an exception "Value cannot be null." — ArsalanK, Feb 05 '16 at 11:43

Andrei · Accepted Answer · 2016-02-05T12:01:52.960

2

Why don't you simplify this, by just not applying any where at all if vendorname is null?

public HttpResponseMessage PostFilter([FromBody] dynamic data)
{
    string[] vendorname = data.vendorname != null
                          ? data.vendorname.ToObject<string[]>()
                          : null;

    var query = context.AllInventories.AsQueryable();
    if (vendorname != null)
    {
        query = query.Where(s => vendorname.Contains(s.VENDORNAME));
    }

    var items = query.Take(500).ToList();
}

edited Feb 05 '16 at 12:01

answered Feb 05 '16 at 11:21

Andrei

55,890
9
87
108

Maybe instead of `query = context.AllInventories.Where(s => vendorname.Contains(s.VENDORNAME));` it could use `query = query.Where(s => vendorname.Contains(s.VENDORNAME));` to allow further query building (or if more query would be built upfront). – Jure Feb 05 '16 at 11:30
I think for multiple where clause condition it will not help me, because for that i need to mention multiple if condition which is not good solution. any other solution? – ArsalanK Feb 05 '16 at 11:34
Why is multiple if and multiple where not a good solution? Query is actually not executed until you call ToList or some other method that materializes data from database (or some other source). – Jure Feb 05 '16 at 11:40
@Jure, you are right, `query = query.Where` was my intention anyway, thanks for the catch – Andrei Feb 05 '16 at 11:45
@ArsalanK, quite contrary, this is a very good approach for multiple where's, because it allows you to keep only necessary conditions in the query, and smaller query may mean cleaner SQL code and faster execution – Andrei Feb 05 '16 at 11:46
Error 1 Cannot implicitly convert type 'System.Linq.IQueryable' to 'System.Data.Linq.Table'. An explicit conversion exists (are you missing a cast?) on this query = query.Where(s => vendorname.Contains(s.VENDORNAME)); – ArsalanK Feb 05 '16 at 11:59
This is a bad way, because you are will fetsh all objects in the Db for a local join. A much better way is to store the list of string in a temporary table, and do an in DB join and fetch the results. I'm used to large data sets, and this solution is not suitable for those at all – user853710 Feb 05 '16 at 12:14
@user853710, well, there is no join here, and even if there were, it would still be possible to define join modifying `query`. The actual DB request won't happen until `ToList` is called, so this is not going to load more objects than required – Andrei Feb 05 '16 at 12:20
Sure, but EF cannot do a in DB join with a local list. EF will fetch the entire table a do a in memory join, no matter if you call ToList or not. The Join here is represented by the contains method. It is even worse, since it is not a hashset but a list and the performance is even worse. It will load evrything – user853710 Feb 05 '16 at 12:28
@user853710, LINQ converts `Contains` method to SQL `IN` clause. Here is [one reference](http://stackoverflow.com/questions/857973/linq-to-entities-sql-in-clause), here is the [other](http://rob.conery.io/2008/02/27/creating-in-queries-with-linq-to-sql/). You can also run this by any SQL profiler to confirm. No memory joins – Andrei Feb 05 '16 at 12:33
Since wehn. I checked it 5 min ago with EF5, EF6 and the profiler says to fetch the entire table. Still, even if were so, .... it would still be a bad way to do since it will still join with not indexed data. What ever you try, It is still not ass good as BulkSaving into a temp table and joining in the server. If you are talking about 10 record, well yes, but if you want scalability or you are joining 100s of records with milions of record in DB, you are running into a bottleneck. Just because you can do something, it does not mean you should do it. – user853710 Feb 05 '16 at 12:41

score 0 · Answer 2 · answered Feb 05 '16 at 11:21

0

Why don't you use a simple if-statement?

IEnumerable<Inventory> inventories = context.AllInventories;
if(vendorname != null)
    inventories = inventories.Where(i => vendorname.Contains(i.VENDORNAME));             
inventories = inventories.Take(500).ToList();

That's much better than hoping that your sql trick works and that the optimizer is clever enough to ignore your pseudo-condition. It's also better to debug.

answered Feb 05 '16 at 11:21

Tim Schmelter

450,073
74
686
939

well i have multiple parameters along with vendorname, for this i need to mention mutliple if conditions. :/ – ArsalanK Feb 05 '16 at 11:31
@ArsalanK: still the best approach in terms of readability, maintainability, testability and performance. – Tim Schmelter Feb 05 '16 at 11:32

Linq to Sql - return all data if 'contains' value is null in where clause

2 Answers2