Get first value from table with where either have the value match or null with LINQ

Question

I have a table from which I want a value like this. I need price info w.r.t. states and vendor, for example, I want to calculate the price for a state, my table has data like either vendor has a price or default price.

So if I look for VendorA in NY I should get 2000. And if I look for VendorA in LA it will give me 1500. I can get this by

public long GetPrice(string State, string Vendor){
    var value = this.context.table.FirstOrDefault(a=>a.vendor == Vendor && a.state == State);

    if(value == null){
    value = this.context.table.FirstOrDefault(a=>a.vendor == null && a.state == State)?.Price;
    }
}

GetPrice("NY","VendorA"); //This should give me 2000.
GetPrice("LA","VendorA"); // This should give me 1500

Please suggest is there is some more optimized way, means Can I get in a single DB call.

What if you look for VendorB in CA ? – Serg Dec 10 '19 at 07:14 — Serg, Dec 10 '19 at 07:14

score 2 · Accepted Answer · answered Dec 10 '19 at 07:45

You could do it in one query by using:

private static long? GetPrice(string state, string vendor)
{
    return table
        .Where(x => x.State == state)
        .OrderByDescending(x => x.Vendor)
        .FirstOrDefault(x => x.State == state)
        ?.Price;
}

Filter out all prices in the matching state, order by vendor descending (to make null values appear last), and pick the first or default item matching the state from it.

score 0 · Answer 2 · answered Dec 10 '19 at 07:23

0

var value = this.context.table.FirstOrDefault(a=>(a.vendor == "VendorA" || a.vendor == null) && a.state == "LA");

answered Dec 10 '19 at 07:23

Nguyễn Văn Phong

13,506
17
39
56

According to your answer var value = this.context.table.FirstOrDefault(a=>(a.vendor == "VendorA" || a.vendor == null) && a.state == "NY"); This will give me 1000, which is incorrect for me. – sagar43 Dec 10 '19 at 07:35
var value = this.context.table.FirstOrDefault(a=>(a.vendor == "VendorA" && a.state== "NY") || ( a.vendor == null && a.state == "LA"); – Nguyễn Văn Phong Dec 10 '19 at 07:39
I am asking about a generic method, not a static one. Please check the question, I had edited this for easiness. – sagar43 Dec 10 '19 at 07:47

StepUp · Answer 3 · 2019-12-10T08:01:59.600

0

Try to use Find method:

var value = this.context.table.Find(a=>a.vendor == "VendorA" && a.state == "LA");

Please, see this performance considerations Find() vs. Where().FirstOrDefault()

UPDATE:

One call to database:

var values = this.context.table
    .Where(a=>a.vendor == Vendor && a.state == State || a.vendor == null 
        && a.state == State).ToList();

edited Dec 10 '19 at 08:01

answered Dec 10 '19 at 07:31

StepUp

36,391
15
88
148

The performance question would be: https://stackoverflow.com/questions/14032709/performance-of-find-vs-firstordefault – juergen d Dec 10 '19 at 07:40
I am not worried about performance of find or where. Can I do this will one DB Call? – sagar43 Dec 10 '19 at 07:49
Yes with this I will get two rows, check my update in the question so that it will make more understandable. – sagar43 Dec 10 '19 at 08:05
@sagar43 I've just seen that you want to make just one call to DB. What another question do you have? – StepUp Dec 10 '19 at 08:07

Get first value from table with where either have the value match or null with LINQ

3 Answers3