×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

LINQ query based on combobox selecteditem

LINQ query based on combobox selecteditem

LINQ query based on combobox selecteditem

(OP)
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:

CODE

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:

CODE

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.

RE: LINQ query based on combobox selecteditem

CODE

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: Database Connection Management
FAQ732-7259: Keeping the UI responsive

RE: LINQ query based on combobox selecteditem

(OP)
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:

CODE

            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:

CODE

    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.

RE: LINQ query based on combobox selecteditem

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.

CODE

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

CODE

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: Database Connection Management
FAQ732-7259: Keeping the UI responsive

RE: LINQ query based on combobox selecteditem

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close