Hi, all.

I need to know to make a 'select' linq query based on conditions that must be true in multiple tables. I'm not sure how to span multiple tables, so I can reference those tables in a where clause. Note that I'm using Prefetch paths.

Here's how it works: - One EventVersion points to one EventAlgorithmVersion. - One EventAlgorithmVersion points to Many AlgorithmVersions. - One AlgorithmVersions points to an Algorithm.

All of this is expressed in the linq query below:

LinqMetaData metaData = new LinqMetaData(DataAdapter);

        IQueryable eventVersionQuery = (from schema in metaData.EventVersion
                                            select schema)
              .WithPath(q => q .Prefetch<EventAlgorithmVersionEntity>(p=>p.EventAlgorithmVersions)
                                              .SubPath(r => r.Prefetch<AlgorithmVersionEntity>(u => u.AlgorithmVersion)
                                                  .SubPath(d => d.Prefetch<AlgorithmEntity>(v => v.Algorithm))))
                                          .Where(a => a.Eventid == associatedEventId);

The problem is that I need to modify my query - or my 'where' clause - to include an 'algorithmId' parameter. To query on that parameter, my query has to be aware of another table called 'Algorithms'. I want my query to look something like this (notice the 'where' clause):

    LinqMetaData metaData = new LinqMetaData(DataAdapter);

        IQueryable eventVersionQuery = (from schema in metaData.EventVersion
                                            select schema)
                                     .WithPath(q => q
                                          .Prefetch<EventAlgorithmVersionEntity>(p => p.EventAlgorithmVersions)
                                              .SubPath(r => r.Prefetch<AlgorithmVersionEntity>(u => u.AlgorithmVersion)
                                                  .SubPath(d => d.Prefetch<AlgorithmEntity>(v => v.Algorithm))))
                                          .Where(a => a.Eventid == associatedEventId && p.AlgorithmId == algorithmId);

How do I do this? The only code I have is in the first query. Thanks in advance.

Hi Frederick,

Since the EventVersion-EventAlgorithmVersion relation is m:1, you can simply use the navigator to filter on the related field. Something like this:

IQueryable eventVersionQuery = 
     (from schema in metaData.EventVersion
      select schema)
           .WithPath(q => q .Prefetch<EventAlgorithmVersionEntity>(p=>p.EventAlgorithmVersions)
                 .SubPath(r => r.Prefetch<AlgorithmVersionEntity>(u => u.AlgorithmVersion)
                        .SubPath(d => d.Prefetch<AlgorithmEntity>(v => v.Algorithm))))

            .Where(a => a.Eventid == associatedEventId && a.EventAlgorithmVersions.AlgorithmId == algorithmId);

This is also valid:

IQueryable eventVersionQuery = 
     (from schema in metaData.EventVersion
      select schema
      where schema.Eventid == associatedEventId && a.EventAlgorithmVersions.AlgorithmId == algorithmId)
           .WithPath(q => q .Prefetch<EventAlgorithmVersionEntity>(p=>p.EventAlgorithmVersions)
                 .SubPath(r => r.Prefetch<AlgorithmVersionEntity>(u => u.AlgorithmVersion)
                        .SubPath(d => d.Prefetch<AlgorithmEntity>(v => v.Algorithm))));

Thanks, Daelmo, but that's exactly what I've already tried. It didn't work for me.

I need to explain this differently.

All of the fields in my "where" clause are properties of the EventVersion table/entity (metaData.EventVersion) because that's the only table my select statement selects from.

I need to select from the eventVersion table, but I also need to select from another table in addition to it - called Algorithm - using a join, or multiple select statement or something. By being able to select across tables, I could retrieve all the values I need from the EventVersion table, while making sure that the Id from the Algorithm table meets certain conditions.

Please notice the "from" part of my select statement, as well as my "where" clause.

 IQueryable eventVersionQuery = (from schema in metaData.EventVersion
                                            select schema)
                                     .WithPath(q => q
                                          .Prefetch<EventAlgorithmVersionEntity>(p => p.EventAlgorithmVersions)
                                              .SubPath(r => r.Prefetch<AlgorithmVersionEntity>(u => u.AlgorithmVersion)
                                                  .SubPath(d => d.Prefetch<AlgorithmEntity>(v => v.Algorithm))))
                                          .Where(a => a.Eventid == associatedEventId && p=> p.AlgorithmId == algorithmId);

Again, how do I do this?

Hi Frederick,

I think I know what you want. To be more clear let me put some examples of the prefetchPath and filtering concepts:

Filtering on a main entity field If you want to filter a simple query you would do something like this in:

var q = from o in metaData.Orders
            where o.Freight > 100
            select o;

which will produce some sql like this:

SELECT * 
FROM Orders 
WHERE Freight > 100

Filtering on a related field If you want to filter on a field that is in a related entity of the main entity you would do something like this:

var q = from o in metaData.Orders
            where o.Customer.Country == "Mozambique"
            select o;

That code, adds automatically a relation behind the scenes. An equivalent query would be:

SELECT O.* 
FROM Orders O
     INNER JOIN Customer C ON O.CustomerId = C.CustomerId
WHERE C.Country = "Mozambique"

Add a prefetchPath A prefetchPath node is something additional that is attached to the final entity/collection results. The simplest example is I want order's collection and for each order I want it's related Customer attached to it:

var q = (from o in metaData.Orders
            where o.Customer.Country == "Mozambique"
            select o)
                .WithPath(orderPath => orderPath
                   .Prefetch(o => o.Customer));

Each prefetchPath generates another query which correlates to the main query. For example the above linq query would generate this SQL:

 -- query 1
SELECT O.* 
FROM Orders O
     INNER JOIN Customer C ON O.CustomerId = C.CustomerId
WHERE C.Country = "Mozambique"
GO

-- query 2
SELECT * 
FROM Customer
WHERE CustomerId IN
   (
      SELECT O.* 
      FROM Orders O
          INNER JOIN Customer C ON O.CustomerId = C.CustomerId
      WHERE C.Country = "Mozambique"
   )

The second query is for the PrefetchPath. Note that the second query is not fetching the Customers from Mozambique (necessarily) , it's fetching the Customers which are listed in Orders which the customer is from Mozambique.

Filtering the PrefetchPath It may be that you want to filter the path results. For instance imagine you want to fetch all Customers, and for each Customer you want the Orders shipped last year. The main query is for Customers. The Orders part is a prefetchPath, and the filter must be applied to the Orders path node. The code would look like:

var q = (from o in metaData.Customer
            select o)
                .WithPath(customerPath => cusomterPath
                   .Prefetch(c => c.Orders)
                      .FilterOn(o => o.ShippedDate.Year == 2011));

So, if I understand correct, you just want to filter the main query with some field is some related entity.

Now in your last post you said:

Frederick wrote:

I need to select from the eventVersion table, but I also need to select from another table in addition to it - called Algorithm - using a join, or multiple select statement or something. By being able to select across tables, I could retrieve all the values I need from the EventVersion table, while making sure that the Id from the Algorithm table meets certain conditions.

which is confusing, because at one hand you want to filter on a related field and add the prefetchPaths (the code I gave to you in my previous post) and at the other hand you say you want to include related fields from other tables in the select list, which is a DynamicList or an AnonymousType, not a entity collection. I think you was confusing about a multi-select and a PrefetchPath.

If it's of some help, I wrote an article about PrefetchPaths concepts and cases: http://www.llblgening.com/archive/2009/10/prefetchpaths-in-depth/