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...

Melisandra

can you give us an example of a query you need to do this in? – Gonzalez

Answer 1

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.

Gonzalez

And if row.a is int? " ?? can't be applied to int and int" – Livorno

@Livorno 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?. – Gonzalez

I can try that. I know you can't as something in an expression, but maybe I could (int?) it. – Livorno

If you know it's an int why do you want to check if it's null? – Gonzalez

Answer 2

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.

Melisandra