Inner join and filter on child tables

What's the expected result?
If you want it to be generic you have to first think about what do you want as an output, and then think about how to get it.

From your question I think you're still on stage 1 :)
You want t Machine list but sometimes a Software list could be handy...

Having them both on the same list will duplicate the Machines info as one machine can have multiple Soltwares.

If you want to get a list of machines and their installed software and be able to filter by all available columns you may want to create some sort of a "View" that has every filterable column in it. Then if all you want are the Machines you'll have to get only their names and apply a distinct, otherwise you'll already have the full software list.

This "View" may actually be created based on the 2nd query you provide... based on the Software table and joining with the Machines table.

Thanks for your response Alex and sorry if I was unclear or didn't go far enough.  As you can probably see, I'm fairly new to EF.

The expected result is a combined list of Machine records (a unique combination of the first query's results and the second query's results).  The only way I can figure out how to do this is to get the results of both queries and iterate over them using a hashtable to combine them and ensure unique records.  That seems very costly.  Also, the software table is just the first related table I'm trying to do this with.  There are 10's of thousands of Machines, dozens of related tables, and many GB of data in this database.  And this is Silverlight -- so I definately want the query to be server side so as not to send all of this data to the client to sort through. That's why my first instinct was to combine the query criteria into one query before it's executed.  Just can't figure out how to do it.  It seems like something like this would be so natural:

query = query.Include("SwLicense").Where(m => m.SwLicense.ProductID == _prodID).Select(lic => lic.vwMachine);

but ObjectQuery has "Include" method -- not EntityQuery and cannot hydrate ObjectQuery client-side.  So I can't figure out how to do this and thought that the clearest way to ask the question would be what's the right way to do an inner join on two tables and return only the parent table's entities and be able to combine that query criterion with others.

Probably still not asking the question in a clear way, but confusion reigns!

Can you attach a .png screen shot of the conceptual data model that you created for the Entity Framework?  What are the defined associations between the elements?  Did you generate navigation properties for the objects between the associated tables?  

Screen snip uploaded.  Just dealing with the first child table at this point.  I am able to navigate -- ie. both of these work:

            var rslt =
                    from pp in ctx.Licenses
                    join s in ctx.vwSitesSystems
                    on
                    new { pp.SubsystemType, pp.SerialNumber }
                    equals
                    new { s.SubsystemType, s.SerialNumber }
                    where (pp.ProductID == "6016")
                    select s;
            foreach (vwSitesSystem sys in rslt)
                Console.WriteLine(sys.SerialNumber);

AND
            var licenses = ctx.Licenses.Include("vwSitesSystem").Where(lic => lic.ProductID == "6016");
            var systems = licenses.Select(lic => lic.vwSitesSystem);
            foreach (vwSitesSystem sys in systems)
            {
                Console.WriteLine(sys.SerialNumber);
            }

a couple of the names are different because this is a different model in a console app.  But the entities and relationship are the same.

My problem is maybe a bigger conceptual one.  Notice there are lots of properties on the main view.  I've already implement search criteria for each of those and it works greats just with query = query.where(...).  Now I'm trying add the ability to search by software product.  Just can't figure out how to do this and am frustrated.  Should I be doing this in a completely different way?  How would I send a complex dynamically created query to a RIA service?  Or instead an array of criteria objects that I parse server-side and put together an ObjectQuery dynamically?  I'm at a loss.



Capture.PNG

"Now I'm trying add the ability to search by software product.  Just can't figure out how to do this and am frustrated"
That picture didn't help very much, so I am joining the frustrated club.

What is PPLicense?  What entity represents "product"?  How is it related?  Is it on the entity model?

Sorry.  
PPLicense == Program Product License == sofware product.  And the picture is the "real" EDM I am using -- as opposed to the one in the console app I provided snippets from -- although the models are the same, but the entity name a little different.  I wil re-do the snippets below.  But in the snippets, I am able to navigate from PPLIcense through the vwSitesSystem navigation property to get the owning vwSiteSystem.  But a console app is not Silverlight client using RIA services.  I can easily call a RIA service to get the serial number of systems with a particular software product, but then I've got two EntityQuery objects (one with this criterion and the original "query" with the other criteria.  How to do this in one query or combine the EntityQuery objects elegantly without doing a Dictionary or something?

            var rslt =
                    from pp in ctx.PPLicenses
                    join s in ctx.vwSitesSystems
                    on
                    new { pp.SubsystemType, pp.SerialNumber }
                    equals
                    new { s.SubsystemType, s.SerialNumber }
                    where (pp.ProductID == "6016")
                    select s;
            foreach (vwSitesSystem sys in rslt)
                Console.WriteLine(sys.SerialNumber);

AND
            var pp = ctx.PPLicenses.Include("vwSitesSystem").Where(lic => lic.ProductID == "6016");
            var systems = pp.Select(lic => lic.vwSitesSystem);
            foreach (vwSitesSystem sys in systems)
            {
                Console.WriteLine(sys.SerialNumber);
            }

I am tripping over the words, so let me rephrase in my own words, and you tell me if I am wrong or right...

You have a machine from a view (vwSiteSystems), and a product (PPLicense), which is related by SerialNumber.  You want to get all the PPLicense entities for a system.  Do you need values from both entities (PPLicenses.SerialNumber, vwSitesSystems.SubsystemType)?  Are you needing an anonymous type (select new {pp.SerialNumber, s.SubsystemType})?

No, I want to get all systems with a particular PPLicense (program/product).  I don't need any values from the PPLicenses table.  Just the systems that have a particular PPLicense.

I really just want to add vwSiteSystem entities to my original query.  A user may want to see all systems in France that have product "6016".  If they do this search, they're going to get a list of machines they can drill into -- vwSiteSystem entities.  

Sorry, I guess i can't even compose English today. :(

The cardinality for association between PPLicense and vwSiteSystem is 1..*.  That would mean that if you used the navigation property for PPLicense entity, you would only get one vwSiteSystem entity.  What you are saying sounds like you have a many-to-many relationship between PPLicense and vwSiteSystem.  Many-to-many relationships usually require an intermediate table between the two tables.

Hmm.  I'l have to think about that.  A particular system may have up to several dozen different program products.  But any individual program product record can have one and only one system parent.

It is true that if I query for a particular program product, I will get only a single parent system for each program product record, but I think the key is I can get a List of the parent systems - which is what I want'.

I doubt it's the most elegant solution, but I've found that I can go ahead and do the queries separately and combine them with a LINQ "intersect" query like this:

                var rslt =
                    (from lst1 in this.AdditionalSystemList
                     select lst1)
                     .Intersect
                     (from lst2 in this.systemList
                      select lst2);

What I was really looking for was a more "LINQ to Entities" type solution.  But I'm just too new at this to come up with a more elegant solution at this point.

Thanks for your help Learned one.  

"But any individual program product record can have one and only one system parent."
When you say that, it makes it sounds like you have a discrepancy between the physical model, and the conceptual model.  I see that the cardinality between those two entities appears to be *..*, even though you say that it is 1..*.  If you don't have the conceptual model cardinality defined correctly, then it makes it more difficult to get the data that you need from the database, through the model.

LearnedOne, you're correct.  I got the cardinality wrong.  So if I fix this it will become clear how to get the parents?

I guess I spoke too soon.  The actual attributes of a program product are a third table (not pictured) linked by the ProgramID.  These are licenses with actual license attributes.  I guess this is one place I went astray before.

If you get the cardinality correct, then the automatically generated navigation properties give you access to the related data in either direction.  I don't understand how that third table fits into your question.

Thanks LearnedOne.  OK, let's assume my cardinality is wrong and fix it.  Can you please show how to do the query I'm trying to do?  The below (between the ???) is wrong syntax and this is the heart of my problem.  

        private void DoQuery()
        {
            EntityQuery<vwSitesSystem> query = ctx.GetVwSitesSystemsQuery();
            // the users wants systems in a specific country
            query = query.Where(s => s.Country == "France");
            // they also want systems less than a specific capacity
            query = query.Where(s => s.CapacityInternal < 10);
            // they also want systems with a specific software product installed
            // ????
            query = query.Where(s => s.PPLicenses.Where(pp => pp.ProductID == "6016"));
            // ???? ..or ????
            ctx.Load(query).Completed += new EventHandler(Query_Completed);
        }

Here is the correct syntax for server side:

var systems = ctx.vwSitesSystems.Where(s => s.Licenses.Any(pp => pp.ProductID == "6016"));

However, it's not possible in a Silverlight client.  "Any" is not allowed client-side.
Can anybody think of another way to skin this in Silverlight?

Guess:

var systems = ctx.vwSitesSystems.Licenses.Where(pp => pp.ProductID == "6016");

Good guess, but Licenses is not exposed on the entity set; only the entities themselves.  But so close.  There's got to be a way to do this!

Guess #2:

var systems = ctx.vwSitesSystems.PPLicenses.Where(pp => pp.ProductID == "6016");

There is a navigation property added to navigate to the PPLicense entity set from vwSitesSystems.

Guess 2 is the same as guess 1.  When I did up the EDM for a quick console program to test this out, I didn't name the "installed software products" table the same.  In the console app it is named "License", in the real EDM in the app it is PPLicense.  Sorry for the confusion that created.  But either way, that navigation property is not exposed on the EntitySet -- only the entities.  I will say that I had to edit the EDM XML manually -- because vwSitesSystems is a view (joining customers and systems) it could not determine a key on -- plus the link is a compound key (SubsystemType/SerialNumber).  But I think I got it right because every query I know should work does.  

I found somebody else with the same exact problem here: http://forums.silverlight.net/forums/p/149885/397424.aspx

I guess if I were to try to restate the question as clearly as possilble after all this it would "How do you compose a dynamic query at runtime in Silverlight that involves multiple views and tables?"  I'm losing what little hair I have left on this.  

I would like assume that you are using Silverlight 3, but you might be using Silverlight 4 by now...

I need to be able to set up a reasonable test for myself that duplicates your situation, so that I can determine the best course of action...

I'm using SL4 with VS2010 Release Candidate.  Haven't upgraded to the RTM yet because it will probably cost me a day and I'm not sure all the toolkits are out yet.

I created a Silverlight 4 application with 2010 RTM, added a model for AdventureWorks, and dropped three tables onto the Entity designer (HumanSources.Employee, HumanResources.EmployeeAddress,  Person.Address).

There is a 1..* cardinality between Employee and EmployeeAddress, and a 1..* cardinality between Address and EmployeeAddress.  The EmployeeAddress table refines the *..* relationship between Employee and Address.

I believe that this scenario mirrors your layout.  There is the vEmployee that shows the relationship between all 3 tables.
Screenshot.png

If I create a view that joins vwSitesSystems and PPLicenses the row count of the view will be multiplied by over 20 -- since systems average around 20 installed program products each.  Plus, then, I would have duplicates in any dynamically created query that does not include a restriction on a particular installed program.

Maybe you could point me in the right direction though if you can show me the client-side EntityQuery for your EDM above that returns Employee entities for all Employees whose address is in Washington state.

I just created a simple data layer class, with the following code, and the attached data model:

using System.Collections.Generic;
using System.Linq;

namespace Common.Examples.Linq
{
    
    public class AdventureWorksDataLayer
    {

        private AdventureWorksEntities _entities;

        public AdventureWorksDataLayer()
        {
            this._entities = new AdventureWorksEntities();
        }

        public List<string> GetEmployeeLoginsForState(string state)
        {
            var q = from a in _entities.Addresses
                    join ea in _entities.EmployeeAddresses on a.AddressID equals ea.AddressID
                    join e in _entities.Employees on ea.EmployeeID equals e.EmployeeID
                    join s in _entities.StateProvinces on a.StateProvinceID equals s.StateProvinceID
                    where s.Name == state
                    select e.LoginID;

            return q.ToList();
        }
    }
}

Select allOpen in new window

Screenshot.png
Debugging-Session.png

Thanks for all your work LearnedOne, but I think still must not be being clear.

I can customize a RIA Service (data layer) method to return a list of systems or employees with any particular search criteria (as you did here). This query is of course running server-side.   The key is that the composition of the query is happening in the client because the end user is composing it.  So the query criteria (the LINQ or EntityQuery with lamda expressions) have to be in the Silverlight client.

Using your method, if we want to add a second criteron (say SalariedFlag), you would need to write another server side query like GetSalariedEmployeeLoginsForState( ).  A third criteron? GetSalariedEmployeesHiredAfterDateForState( ).  The number of methods would be exponential and it would unmanagable both server and client-side.

Or instead, doing the query on the client side we can easily do: (syntax not checked)

    EntityQuery<Employee> query = context.GetEmployeesQuery();

and then, oh, they want a filter on salaried:
    query = query.Where(e => e.SalariedFlag == true);

and then, they want a filter on hire date:
   query = query.Where(e => e.HireDate > whatever);

now, we need to add the criteron for state
    query = query.Where(e => e.EmployeeAddresses.????

Either
1) there's a way to do this client-side like I am trying.  Or,
2) it's not possible with the current state of RIA Services, so I would have to skin this another way such as:
     a) send the dynamically composed list of criteria to the server and compose the query there, or
     b) do separate queries and find the intersection of the results???

Ok, so you made me create a Silverlight 4 (with RIA Services) project, and go through all the hoops, just to get to a place where I can experiment *BIG GRIN*.  I was hoping that I could avoid it, but I clearly can't (d'oh).

1) Created an employee domain service server side.

2) Created an instance of the domain service context client-side.

3) Started with a nice simple query, to test your first query about employees who work in Washington:

       public void LoadEmployees(string state)
        {
            var context = new EmployeeDomainContext();

            EntityQuery<EmployeeAddress> query = context.GetEmployeeAddressesQuery().Where(e => e.Address.StateProvince.Name == state);

            LoadOperation<EmployeeAddress> loadEmployees = context.Load<EmployeeAddress>(query);
            loadEmployees.Completed += new EventHandler(loadEmployees_Completed);
        }

        void loadEmployees_Completed(object sender, EventArgs e)
        {
            var loadOperation = sender as LoadOperation<EmployeeAddress>;
            List<EmployeeAddress> employeeList = new List<EmployeeAddress>(loadOperation.Entities);
        }

Select allOpen in new window

Oh, you're so close LearnedOne!  But remember we need to start with the EmployeesDomainService and return <Employee> entities because we don't know what attributes of an Employee the user will want to search on.

EntityQuery<Employee> query = context.GetEmployeesQuery().Where(e => .so and so

While you're at it, make it salaried imployees that live in Washington.  

Thanks so much for staying with me on this.

Full points LearnedOne if you'll admit it's not possible and you give me your best suggestions for a workaround.

A for effort.