If control in linq query entity framework

Question

,Hi

List<Claimant> list = (from c in CacheManager.ClaimantList.List
where 

     c.Fullname.Replace("i", "İ").Replace("ı","I").ToUpper().Contains(searchKey.Replace("i", "İ").Replace("ı", "I").ToUpper())
  || c.IdentityNumber.Replace("i", "İ").Replace("ı", "I").ToUpper().Contains(searchKey.Replace("i", "İ").Replace("ı", "I").ToUpper())
  || c.TaxNumber.Replace("i", "İ").Replace("ı", "I").ToUpper().Contains(searchKey.Replace("i", "İ").Replace("ı", "I").ToUpper())

 select c).Take(20).ToList<Claimant>();

If Taxnumber is null or "" i get exception because of null data.

My question:

How can i set Taxnumber == 0 , if Taxnumber is null or "" ?

Any help will be appreciated.

Thanks.

Answer 1

If you just want certain columns and you don't need the whole Claimant object, you can put something into a select

from c in ClaimantList
where (...)
select new {c.FullName, c.IdentityNumber, TaxNumber = String.IsNullOrEmpty(c.TaxNumber) ? "0" : c.TaxNumber}

You now have an anonymous type with three fields: original FullName, original IdentityNumber and modified TaxNumber.

---

You could also create a new Claimant for each value by calling its constructor each time:

from c in ClaimantList
where (...)
select new Claimant() {FullName = c.FullName, IdentityNumber = c.IdentityNumber, TaxNumber = String.IsNullOrEmpty(c.TaxNumber) ? "0" : c.TaxNumber}

(I don't know what the constructor of Claimant is like, you might do this slightly differently and set those values in the constructor rather than in the initializer. Or you could write a new constructor.) If the constructor does something non-trivial, it might be wasteful of resources to call it this many times.

In either case, it is stylistically nice to put the conversion (String.IsNullOrEmpty(TaxNumber) ? "0" : TaxNumber or whatever) into its own function somewhere, so that you can change what conversion you do later (see below), and don't have to repeat the code in multiple places.

---

Another option is that you could use the objects you have, but change the TaxNumber as required. LINQ isn't really the best way of doing this, as you are basically applying side-effects. (LINQ likes to supply a new container with the right data in it, rather than change the data in the original container.) You should really run foreach outside of the Linq code, like this:

List<Claimant> list = from ...
                      where ...
                      select ...

foreach (Claimant claimant in list)
{
    if (String.IsNullOrEmpty(claimant.TaxNumber))
    { claimant.TaxNumber = "0"; }
}

Ideally you do this after the Take(20) so that you only do it the number of times you need.

---

Again, putting the conversion in a function will be neater and nicer:

List<Claimant> list = from ...
                      where ...
                      select ...

foreach (Claimant claimant in list)
{
    claimant.TaxNumber = NormalizeTaxNumber(claimant.TaxNumber); }
}

// ...

public static string NormalizeTaxNumber(string n)
{
    return String.IsNullOrEmpty(n) ? "0" : n;
}

---

However, if you have gone down this route, the next option is to add a method to Claimant which does this change:

List<Claimant> list = from ...
                      where ...
                      select ...

foreach (Claimant claimant in list)
{
    claimant.NormalizeTaxNumber();
}

public class Claimant
{
    // ...

    public void NormalizeTaxNumber()
    {
        if (String.IsNullOrEmpty(TaxNumber))
        { TaxNumber = "0"; }
    }
}

and to call this from the foreach loop.

---

Or, use a different property than TaxNumber:

public class Claimant
{
    // ...

    public string NormalTaxNumber
    {
        get { return String.IsNullOrEmpty(TaxNumber) ? "0" : TaxNumber; }
    }
}

The advantage of the first strategy is that you only have to call it once - the advantages of the second are that it keeps the original value still available (in the TaxNumber property), and that if you have a bunch of Claimant objects, you don't have to remember whether you have normalized them or not.

---

You could also use a combination of the two methods: add a new property NormalTaxNumber which converts TaxNumber when needed, and which caches the result so that conversion doesn't have to be done again.

public class Claimant
{
    // ...

    private string m_normalTaxNumber;

    private string ConvertedTaxNumber
    {
        get { return String.IsNullOrEmpty(TaxNumber) ? "0" : TaxNumber; }
    }

    public string NormalTaxNumber
    {
        get
        {
            if (m_normalTaxNumber == null)
            { m_normalTaxNumber = ConvertedTaxNumber; }
            return m_normalTaxNumber;
        }
    }
}

This only does the calculation once, keeps the original still available, and doesn't require to remember if you've called it before or not. If TaxNumber (the original value) isn't readonly, this is probably more trouble than it's worth as you will have to invalidate the cached value.

---

If you are never, never going to need to know whether the TaxNumber was originally null, empty or "0", the best advice (and the last strategy) is to convert to the form you want as soon as possible, preferably in the Claimant constructor, if the value of TaxNumber is known there.

If you are getting Claimant objects from a database, you could take this to its logical limit by doing the conversion on the database, either in a stored proc or in a view. If you are using LinqToSql to get the list of Claimant objects, a view could work for you but a stored proc might not.

---

I have assumed throughout that you want TaxNumber to be available as a string, and that by 0 you mean the string "0". If this isn't the case, and you actually want convert to an int (or similar), some of the strategies above will still work. You can select the converted value as an int in an anonymous type, you can make a new property NormalTaxNumber (with int type), whether cached or not cached, or you can do the conversion upon creation of a Claimant object. Obviously you can't overwrite the string TaxNumber with an int.

If you are parsing the string to an int, things like NormalTaxNumber with caching become more worthwhile, because the conversion from string to int is more costly. (Checking for null or "" is actually very fast, however I thought it valuable to go through some different options anyway.)

Note that almost all of these should still be available to you if you can't modify the Claimant class. You can't write a new constructor, but you can write a factory method which encapsulates exactly the same behavior as a constructor. You can add NormalizeTaxNumber as an extension method, and while you can't add properties, you can add extension methods with no arguments which work almost exactly like properties.

My last observation is that "0" might not necessarily be a better value for missing data than null or "". Either of the latter is more commonly used to indicate missing values, especially null. Perhaps you should choose one of those as the default (and maybe apply one of the above strategies in reverse to enforce it as the only 'N/A' value?) If it's just a question of it looking nice when you print the object out, you could override ToString() to swap in the "0" only when needed.

Answer 2

You can do:

from c in CacheManager.ClaimantList.List
let taxNumber = c.TaxNumber == null || c.TaxNumber == "" ? "0" : c.TaxNumber
where ...

and then use the taxNumber variable rather than the column.