Does anyone know how to reproduce an NVL() function in linq

Question

So I need to do a query where I need a bunch of NVL's but I need to do these in linq (if it helps the db backend is BD2 and we are using subsonic) I searched the web for "NVL linq" and didn't really find anything useful so I am asking here,

Thanks for your help...

can you give us an example of a query you need to do this in? — Matt Ellen, Apr 06 '11 at 14:38

Matt Ellen · Accepted Answer · 2011-04-06T14:47:55.573

10

You can use the null coalescing operator ??:

var abs = from row in table
          select new {a = row.a ?? "default", b = row.b};

The operator looks at the value on the left and if it is null then it uses the value on the right. So in the example if row.a is null, then a becomes "default".

This assumes that row.a is a string.

edited Apr 06 '11 at 14:47

answered Apr 06 '11 at 14:40

Matt Ellen

11,268
4
68
90

And if `row.a` is `int`? " ?? can't be applied to int and int" – JonathanPeel Aug 28 '20 at 10:50
@JonathanPeel I would expect that any DB query will return `int?` not plain `int`. If you are returning plain `int`, you can cast it to `int?`. – Matt Ellen Aug 28 '20 at 12:27
I can try that. I know you can't `as` something in an expression, but maybe I could `(int?)` it. – JonathanPeel Aug 31 '20 at 06:14
If you know it's an `int` why do you want to check if it's null? – Matt Ellen Aug 31 '20 at 06:25

score 0 · Answer 2 · answered Apr 08 '11 at 12:58

If anyone wants to know how I ended up doing this I didn't use the null coalesce operator... It was even simpler than that. Maybe I didn't explain my self clearly but what I needed to do was to say that if the value is null then means I want to include that result. Check it out.

 possibleVendors = (from vndMapping in db.COMPANIES_VND_MAPPINGS
                     join v in db.COMPANIES_CMP_COMPANIES on vndMapping.VENDOR_ID equals v.COMPANY_ID
                     where
                     !(from ex in db.COMPANIES_VND_MAPPINGS
                        where (ex.OEM_ID == oemId || ex.OEM_ID == null)
                        && (ex.MODEL_ID == modelId || ex.MODEL_ID == null)
                        && (ex.MODALITY_ID == modalityId || ex.MODALITY_ID == null)
                        && (ex.CLASS_ID == productTypeId || ex.CLASS_ID == null)
                        && ex.EXCLUDE.ToUpper().Equals("Y")
                        select ex.VENDOR_ID).Contains(vndMapping.VENDOR_ID)
                     && (vndMapping.OEM_ID == oemId || vndMapping.OEM_ID == null)
                     && (vndMapping.MODEL_ID == modelId || vndMapping.MODEL_ID == null)
                     && (vndMapping.MODALITY_ID == modalityId || vndMapping.MODALITY_ID == null)
                     && (vndMapping.CLASS_ID == productTypeId || vndMapping.CLASS_ID == null)
                     select new
                     {
                       vndMapping.VENDOR_ID,
                       v.COMPANY_NAME
                     }).Distinct().OrderBy(x => x.VENDOR_ID).ToDictionary(x => x.VENDOR_ID, x => x.COMPANY_NAME);

Now keep in mind - I had a few limitations in that I didn't design the business logic (obviously) or the database - Someone else might have a better way to do this if you had complete control of everything, this seems to work though.

Does anyone know how to reproduce an NVL() function in linq

2 Answers2