CODEWITHSUNDEEP
c#linq
Sheri Trager
Sheri Trager

Reputation: 852

Linq check for null and replace null value in orderby

I have a linq query where sometimes the string field, product name, may be empty, null. This is a new field in the table and if data hasn't been entered, then I get an error when running the query. What I need to do is check for null and if null replace the null/empty value with the text value from a drop down list. I've tried several things but can't get it to work. 

public IQueryable<Product> BindProduct([Control("ddProductName")] int? ProductNameId)
{
   var prod = from c in _DbContext.Products
      where c.ProductNameId == ProductNameId
      orderby string.IsNullOrEmpty(c.ProductName) ? ddProductName.SelectedItem.Text : c.ProductName,
      c.ItemNumber
      select c;
      return prod;
}

CHANGED TO:

 public IQueryable<Product> BindProduct([Control("ddProductName")] int? ProductNameId)
    {
        var prodName = ddProductName.SelectedItem.Text;
        var prod = from c in _DbContext.Products
                   where c.ProductNameId == ProductNameId
                   let sortName = c.Name ?? prodName
                   orderby sortName, c.ItemNumber
                   select new { c, sortName };
        return prod;

    }

UPDATE: I don't think I was clear. What I need to do is check for null and if null replace the null/empty value with the text value from a drop down list.

Upvotes: 4

Views: 11060

Answers (3)

Sheri Trager
Sheri Trager

Reputation: 852

The answer came from a peer of mine, Brad. For anyone else who is wondering how to replace a null/empty value using linq, see below.

public IQueryable<Product> BindProduct([Control("ddProductName")] int? ProductNameId)
{
    var prodName = ddProductName.SelectedItem.Text;
    var prod = _DbContext.Products.Where(c => c.ProductNameId == ProductNameId).ToList().Select
    (p =>
    {
        p.Name = string.IsNullOrEmpty(p.Name) ? prodName : p.Name;
        return p;
    }).OrderBy(p => p.Name).ThenBy(p => p.ItemNumber).AsQueryable();

    return prod;
}

Upvotes: 2

Valera Kolupaev
Valera Kolupaev

Reputation: 2315

"Let" keyword should do the trick.

Here is a LINQPad example:

var products = Enumerable.Range(0, 100).Select(q => new {ProductNameId = 1, ProductName = (q % 15 == 0 ? null : (q % 3 == 0 ? "Fizz" : (q % 5 == 0 ? "Buzz": q.ToString()))), ItemNumber = q});
var ddProductName_SelectedItem_Text = "FizzBuzz";
var ProductNameId = 1;
var prod = from c in products
                     where c.ProductNameId == ProductNameId
                     let sortName = c.ProductName ?? ddProductName_SelectedItem_Text
                     orderby sortName, c.ItemNumber
                     select c;
return prod; //prod.Dump(); //for linqpad debugging

Upvotes: 7

wagesj45
wagesj45

Reputation: 145

When you call the orderby in your query, LINQ to SQL will attempt to resolve it into an order by statement in SQL. Since SQL has no method for a conditional order by statement, the method will fail to compile from LINQ.

What you want can be achieved, but it will require ordering the set client side instead of server side. My suggestion would be to run the query without the order by, and at the point that you need to order, first transform the collection to an IEnumerable collection. This will pull into memory the results from the IQueryable, and you'll be able to run the orderby against the object collection.

The basic flow will be something like this: queryable.AsEnumerable().OrderBy()

The big takeaway here is to remember that LINQ will try to transform an IQueryable into a SQL query at the point at which you request an enumeration over the collection. There is a pretty good high level explanation here: http://blog.worldmaker.net/2013/mar/11/mini-lecture-linq-visualization/.

Upvotes: 1

Related Questions