locked
Selecting records where some criteria in child table is true? RRS feed

  • Question

  • I have a table 'workorder' with key 'workordernumber'.

    I have another table 'workdone' with a key of 'workordernumber' and 'workdonedate'.

    What I need to do is to build a query that returns all workorders that match various criteria. This is being driven by a general purpose search form, with dozens of fields, of which the user can choose whatever he likes.  To make this more complex, the form request is submitted via AJAX, which I need to parse and use to construct a query, which I'm doing using PredicateBuilder.  All of that is working fine, except for 'workdonedate'.

    The problem is that 'workdonedate' doesn't exist in workorder. If the user selects a workdonedate range, what I need to return are workorders for which there exist workdone records for that workordernumber with workdonedates within the range.

    We're doing this in our older version by constructing a SQL statement as a string. If we have a workdonedate range, we include a "left outer join workdone on workorder.workordernumber = workdone.workordernumber" clause after the 'from' clause, include 'and workdonedate between' to the where clause, and do a 'select workorder.*' to finish up.

    But how to do this in Entity Framework?

    And more complicated yet, how to do this in Entity Framework by dynamically constructing an expression tree?

    ====

    A follow-up :

    An alternative method for dealing with this, in plain SQL is, instead of doing a join on workdone and a select workorder.* to strip it out, would be to do a 'select * from workorder' and add a 'where exists (select 0 from workdone where ...)'.  It'd be conceptually cleaner, I think, in predicate builder, because the base type of the query would stay 'workoder', instead of being something dynamic, but I have no idea how to do a 'where exists (subquery)' in EF, either.


    Monday, June 30, 2014 4:21 PM

Answers

  • What seems to be easiest is to define an association between parent and child tables, in my model, and to use their navigation properties in the query.

    You can use the navigation properties in E-SQL too and build dynamic E-SQL that I suspect is more than strightforward. Yeah, I have used PredicateBuilders too in Linq queries againt the EF model that just couldn't do what I needed done. However, I could get it done with E-SQL that has all the power as if I was doing it with T-SQL.

    http://msdn.microsoft.com/en-us/library/vstudio/bb896321(v=vs.100).aspx#_ESQL

    • Marked as answer by Fred Bao Tuesday, July 8, 2014 6:10 AM
    Tuesday, July 1, 2014 8:19 PM

All replies

  • Sometimes, you just can't do it with Linq. You can use E-SQL to dynamically build the query, use the datareader and populate entities/objects on the model and return them.

    http://msdn.microsoft.com/en-us/library/vstudio/bb399560(v=vs.100).aspx

    Entity SQL is a storage-independent query language that is similar to SQL. Entity SQL allows you to query entity data, either as objects or in a tabular form. You should consider using Entity SQL in the following cases:

    • When a query must be dynamically constructed at runtime. In this case, you should also consider using the query builder methods of ObjectQuery instead of constructing an Entity SQL query string at runtime.
    • When you want to define a query as part of the model definition. Only Entity SQL is supported in a data model. For more information, see QueryView Element (MSL)
    • When using EntityClient to return read-only entity data as rowsets using a EntityDataReader. For more information, see EntityClient Provider for the Entity Framework.
    • If you are already an expert in SQL-based query languages, Entity SQL may seem the most natural to you.

    You can do left/right joins do subqueries and the whole nine yards.

    http://msdn.microsoft.com/en-us/library/vstudio/bb387118(v=vs.100).aspx

    http://msdn.microsoft.com/en-us/library/vstudio/bb738684(v=vs.100).aspx

    It's been a couple of times where I had to abondon Linq and switch over to using E-SQL.

    Monday, June 30, 2014 6:30 PM
  • What seems to be easiest is to define an association between parent and child tables, in my model, and to use their navigation properties in the query.

    var wos = myDbContext.workoders
        .Where(wo => wo.workdone.workdonedate == DateTime.Now();

    or:

    var wos = from wo in myDbContext
        where wo.workdone.workdonedate == DateTime.Now()
        select wo;

    Of course, I'm building Expressions using PredicateBuilder, so it's a bit more complicated. But it follows fairly straightforwardly.

    Tuesday, July 1, 2014 7:42 PM
  • What seems to be easiest is to define an association between parent and child tables, in my model, and to use their navigation properties in the query.

    You can use the navigation properties in E-SQL too and build dynamic E-SQL that I suspect is more than strightforward. Yeah, I have used PredicateBuilders too in Linq queries againt the EF model that just couldn't do what I needed done. However, I could get it done with E-SQL that has all the power as if I was doing it with T-SQL.

    http://msdn.microsoft.com/en-us/library/vstudio/bb896321(v=vs.100).aspx#_ESQL

    • Marked as answer by Fred Bao Tuesday, July 8, 2014 6:10 AM
    Tuesday, July 1, 2014 8:19 PM