LINQ query based on combobox selecteditem

[kristofdielis]

Hi,

I have two comboboxes, one containing all sites, the other contains all units. The units depends on the sites, so if a site is selected, only those units that belong to the selected site should be fetched.

First I check if there is a selected item:

WPF.Model.SITE site = (cbxSites.SelectedItem != null ? (WPF.Model.SITE)cbxSites.SelectedItem : null);

So site either contains a valid SITE instance, or null, so now we can check that in the LINQ query:

var units = from u in db.UNIT
   where site == null || (site != null && u.site_id == site.site_id)
   orderby u.name
   select u;

Or, I thought that would work, but I get a NullValueException on the units IEnumerable, if site is null.

Any suggestions on how to improve the provided query?

Thx.

 

[jmeckley]

private IEnumerable<Unit> GetUnitsFor(Site site)
{
   if (site == null)
   {
      return from u in db.UNIT
         orderby u.name
         select u;
   }
   return from u in db.UNIT
      where u.site_id == site.site_id
      orderby u.name
      select u;
}

Jason Meckley
Programmer

faq855-7190
faq732-7259

 

[kristofdielis]

Hi Jason,

Thanks for the response, but I don't see that as truely making use of the strengths of LINQ*.

The code I posted is the easiest version, deeper in, there would something like this:

            var sections = from d in db.DRAWING
                           where (site == null || d.site_id == site.site_id)
                           && (unit == null || d.unit_id == unit.unit_id)
                           && (section == null || d.section_id == section.section_id)

If you're doing that with an if structure, you're going to end up with monstrous code.

With SQL, the desired select is easy:

	SELECT *
	FROM [DRAWING]
	WHERE 1 = 1
	AND (@site_id is null OR [site_id] = @site_id)
	AND (@unit_id is null OR [unit_id] = @unit_id)
	AND (@section_id is null OR [section_id] = @section_id)
	AND (@drawing_id is null OR [drawing_id] = @drawing_id)

In the meantime, I solved it by using a stored procedure, which I call with the correct parameters, but I was hoping I could do that directly with LINQ.

* If that's even possible.

 

[jmeckley]

linq is a way to chain deferred execution together using a compact syntax. so needing a if statement to distinguish between linq queries or where clauses doesn't detract from the strengths of linq.

you also have to remember not to mix object queries with sql queries. Linq abstracts the details of what and how a collection is queried. sometimes too well and you end up mixing linq2objecs with linq2sql which causes problems like the one you see above.

another approach to solving this with, less redundancy, is using the extension methods.

private IEnumerable<Unit> GetUnitsFor(Site site)
{
   var units = db.Unit;
   
   if (site != null)
   {
      units = units.Where(u=>u.site_id = site.site_id
   }
   return units.OrderBy(u=>u.name).Select(u=>u.name);
}

and if you wanted to expand on this even more you could encapsulate this query into a query object and use decorators to apply a where clause as needed. but that is probably overkill for this simple query.

btw you can simplify the proc by dropping where 1=1

SELECT name
FROM [DRAWING]
WHERE 
    (@site_id is null OR [site_id] = @site_id)
AND (@unit_id is null OR [unit_id] = @unit_id)
AND (@section_id is null OR [section_id] = @section_id)
AND (@drawing_id is null OR [drawing_id] = @drawing_id)

Jason Meckley
Programmer

faq855-7190
faq732-7259

 

[kristofdielis]

I fixed it by creating Stored Procedures and adding them to the LINQ model.