none
Linq to return data from multiple tables in C# RRS feed

  • Question

  • I have 3 linked tables, here is a subset of the fields and how they are linked:

    Projects:

    • ProjectName
    • Description

    ProjectTasks:

    • TaskNumber
    • ProjectName

    ProjectHours:

    • TaskNumber

    Projects "ProjectName" linked to ProjectTask  "TaskNumber" linked to ProjectHours "TaskNumber"

    All this is being queried using a WCF DataService.

    I want to return Projects/ProjectTasks/Projecthours.

    This returns a list with the tables broken down which is good:

      var ProjectQuery = _context.Projects.Expand("ProjectTasks/ProjectHours").OrderBy(x => x.ProjectName).ToList();

    Now I want to return the same order of tables but only for TaskNumber 516

    I tried this:

     var q32 = (from c in ProjectQuery from o in c.ProjectTasks where o.TaskNumber ==  457 select c ).ToList();

    It returns the Project holding the Tasknumber and no other Projects as it should but

    If I expand q32 in debug mode it has all the ProjectTasks for the specific Project.

    How can I return only the ProjectTasks that has 457 as the TaskNumber and all Projecthours the are associated for the TaskNumber? 

    Monday, November 23, 2015 8:21 PM

Answers

  • Again... you propably didn't read that:

    Now i understand, you're not having trobles with the LINQ, you have troubles understanding, that when you query an Entity and Expand (or Include) the NavigationProperties (Referenced Tables), you cannot have the NavigationProperties filtered as well, cause that would mean an Inconsistent state of an Entity. So if you need to have an Project which only has Task #var# on it, You'll need to create a Project (get it wothout Include or Expand) and then add only the Task you want and then DETATCH that thing from the EF so it does not get tracked and messes everything up.

    • Marked as answer by Peter Viau Tuesday, November 24, 2015 3:43 PM
    Tuesday, November 24, 2015 3:34 PM

All replies

  • >>It returns the Project holding the Tasknumber and no other Projects as it should but If I expand q32 in debug mode it has all the ProjectTasks for the specific Project.

    Feeling a bit confused about this, because as you mentions at first, it works as expected, while you say it does not work later. Could you please share your detail tables information with us, we would help make a test with it to see if we could reproduce this strange scenario. Or you could share the whole project with us.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, November 24, 2015 5:34 AM
    Moderator
      • >>It returns the Project holding the Tasknumber and no other Projects as it should but If I expand q32 in debug mode it has all the ProjectTasks for the specific Project.

        Feeling a bit confused about this, because as you mentions at first, it works as expected, while you say it does not work later. Could you please share your detail tables information with us, we would help make a test with it to see if we could reproduce this strange scenario. Or you could share the whole project with us.

        The Projects Table has these fields:

        Projects:

        • ProjectName
        • Description
        • Status (Can be part of a filter, is it Active,Beta etc.. This part I have working.)

        The ProjectTasks Table has the these fields that the user might want to filter:

        ProjectName (has a relationship to Projects table)
        TaskName
        ActivityType
        Status (could be part of a filter)
        Contributors (could be part of a filter)
        etc...

        I have a Treeview that is populated with Projects then ProjectTasks
        The Projects filters work fine.

        What I want to do is also filter the ProjectTasks table so that it returns ProjectTasks that are assigned to a specific Contributor or have a Status of "Open" for example. 

      This query brings back all Projects and ProjectTasks.

      var ProjectQuery = _context.Projects.Expand("ProjectTasks/ProjectHours").OrderBy(x => x.ProjectName).ToList();

      But lets say I want to return ProjectTasks that has a TaskNumber = 457 for simplicity.

      I use this to search for TaskNumber of 457.

       var q32 = (from c in ProjectQuery from o in c.ProjectTasks where o.TaskNumber ==  457 select c ).ToList();

      It returns the Project that contains the ProjectTask with TaskNumber of 457, BUT it also returns all of the ProjectTasks for the Project which I don't want.


    Tuesday, November 24, 2015 1:19 PM
  • Please elaborate the Foreign and Primary Keys of your tables.

    have you tried

    from c in ProjectQuery where c.TaskNumber == 457 from o in c.ProjectTasks where o.TaskNumber ==  457 select c 
    
    or
    
    from c in ProjectQuery from o in c.ProjectTasks where o.TaskNumber ==  457 && c.TaskNumber == 457 select c 
    
    or 
    
    from c in ProjectQuery from o in c.ProjectTasks where o.TaskNumber ==  457 && c.TaskNumber == o.TaskNumber select c 

    also couldn't you just do

    var ProjectQueryById = _context.Projects.Single(p=>p.TaskNumber == #SOMEVARIABLE#).Expand("ProjectTasks/ProjectHours").OrderBy(x => x.ProjectName).ToList();

    or

     var q32 = (from c in ProjectQuery where o.TaskNumber ==  457 select c ).ToList();


    Tuesday, November 24, 2015 2:02 PM
  • Please elaborate the Foreign and Primary Keys of your tables.

    have you tried

    from c in ProjectQuery where c.TaskNumber == 457 from o in c.ProjectTasks where o.TaskNumber ==  457 select c 
    
    or
    
    from c in ProjectQuery from o in c.ProjectTasks where o.TaskNumber ==  457 && c.TaskNumber == 457 select c 
    
    or 
    
    from c in ProjectQuery from o in c.ProjectTasks where o.TaskNumber ==  457 && c.TaskNumber == o.TaskNumber select c 

    also couldn't you just do

    var ProjectQueryById = _context.Projects.Single(p=>p.TaskNumber == #SOMEVARIABLE#).Expand("ProjectTasks/ProjectHours").OrderBy(x => x.ProjectName).ToList();

    or

     var q32 = (from c in ProjectQuery where o.TaskNumber ==  457 select c ).ToList();

    None of those queries work because TaskNumber is in ProjectTasks not  in Projects. I include Image of the tables below.


    Tuesday, November 24, 2015 2:14 PM
  • Foreign Keys?!
    Tuesday, November 24, 2015 2:18 PM
  • Sorry, Here you go:

    Primary Key Table : "Projects" Foreign Key Table "ProjectTasks"

    ProjectName -> ProjectName

    Primary Key Table: "ProjectTasks" Foreign Key Table "ProjectHours"

    TaskNumber -> TaskNumber

    Tuesday, November 24, 2015 2:23 PM
  • from p in ProjectQuery where ProjectTasks.TaskNumber == #var# 

    should still work, the ProjectQuery result would have a field TaskNumber from the ProjectTasks Table.

    otherwise:

    from p in _context.Projects.Where(pred=>pred.ProjectName (from t in _context.ProjectTasks where TaskNumber == #var# select t.ProjectName).Contains(pred.ProjectName)).Expand("ProjectTasks/ProjectHours") // OrderBy omitted since it should be only one

    or even

    _context.ProjectTasks.Include("Projects").Include("ProjectHours").Single(p=>p.TaskNumber == #var#).Project;

    or

    _context.Projects.Expand("ProjectTasks/ProjectHours").Where(x=>x.ProjectTasks.TaskNumber == #var#).OrderBy(x => x.ProjectName).ToList();

    Now i understand, you're not having trobles with the LINQ, you have troubles understanding, that when you query an Entity and Expand (or Include) the NavigationProperties (Referenced Tables), you cannot have the NavigationProperties filtered as well, cause that would mean an Inconsistent state of an Entity. So if you need to have an Project which only has Task #var# on it, You'll need to create a Project (get it wothout Include or Expand) and then add only the Task you want and then DETATCH that thing from the EF so it does not get tracked and messes everything up.


    • Edited by MDeero Tuesday, November 24, 2015 3:04 PM
    Tuesday, November 24, 2015 2:31 PM
  • All of those won't compile.

    I tried this one.

    _context.Projects.Expand("ProjectTasks/ProjectHours").Where(x=>x.ProjectTasks.TaskNumber == #var#).OrderBy(x => x.ProjectName).ToList();

    Like this:

       var fromforum = _context.Projects.Expand("ProjectTasks/ProjectHours").Where(x => x.ProjectTasks.TaskNumber == 457).OrderBy(x => x.ProjectName).ToList();

    And it returns:

    Severity Code Description Project File Line
    Error CS1061 'DataServiceCollection<ProjectTask>' does not contain a definition for 'TaskNumber' and no extension method 'TaskNumber' accepting a first argument of type 'DataServiceCollection<ProjectTask>' could be found (are you missing a using directive or an assembly reference?) DevTraxClient Visual Studio 2015\Projects\DevTraXClient\DevTraxClient\Projects\ProjectViewModel.cs 331

    Tuesday, November 24, 2015 2:56 PM
  • Yeah... ofc, its a collection, my bad

    var fromforum = _context.Projects.Expand("ProjectTasks/ProjectHours").Where(x => x.ProjectTasks.SingleOrDefault(xx => xx.TaskNumber == 457) != null).OrderBy(x => x.ProjectName).ToList();

    But i think you should read the fromer post with its edit again, I believe you think that Navigation Properties can be filtered on the Entity containing them (making referecens magically disappear).
    • Edited by MDeero Tuesday, November 24, 2015 3:07 PM
    Tuesday, November 24, 2015 3:03 PM
  • Runtime error:

    ex = {"The method 'SingleOrDefault' is not supported."}

    Tuesday, November 24, 2015 3:06 PM
  • well if its really not there:

    var fromforum = _context.Projects.Expand("ProjectTasks/ProjectHours").Where(x => { try { x.ProjectTasks.Single(xx => xx.TaskNumber == 457); return true;} catch(Exception ex) { return false} }).OrderBy(x => x.ProjectName).ToList();

    If Single is not available either

    var fromforum = _context.Projects.Expand("ProjectTasks/ProjectHours").Where(x => x.ProjectTasks.Where(xx => xx.TaskNumber == 457).Count() > 0).OrderBy(x => x.ProjectName).ToList();


    • Edited by MDeero Tuesday, November 24, 2015 3:13 PM
    Tuesday, November 24, 2015 3:11 PM
  • The first one won't compile.

    The second one returns:

    {"The expression ([10007].ProjectTasks.Where(xx => (xx.TaskNumber == 457)).Count() > 0) is not supported."}

    Tuesday, November 24, 2015 3:16 PM
  • OMFG, what kinda thing are you using?

    https://msdn.microsoft.com/library/cc646887(v=vs.90).aspx

    Even the SingleOrDefault method should be available, are you missing any References?

    How about this?

     (from o in c.ProjectTasks where o.TaskNumber ==  457 select o.Project).ToList();

    • Edited by MDeero Tuesday, November 24, 2015 3:25 PM
    Tuesday, November 24, 2015 3:19 PM
  • OMFG, what kinda thing are you using?

    https://msdn.microsoft.com/library/cc646887(v=vs.90).aspx

    Even the SingleOrDefault method should be available, are you missing any References?

    LOL!

    No missing references.

    Queries are run against a WCF Data service which seems to have some limitations with Linq.

    Tuesday, November 24, 2015 3:23 PM
  • some -> its pretty useless it seems

     (from o in _context.ProjectTasks where o.TaskNumber ==  457 select o.Project).ToList();

    you may need _context.ProjectTasks.Expand("...")

    Oh and if this still don#t work, step away from LINQ to Entities and try to do it with LINQ to Objects:

    _context.Projects.Expand("ProjectTasks/ProjectHours").OrderBy(x => x.ProjectName).ToList().Where(x=>x.ProjectTasks.TaskNumber == 457);




    • Edited by MDeero Tuesday, November 24, 2015 3:31 PM
    Tuesday, November 24, 2015 3:26 PM
  •   var lasttry =  (from o in _context.ProjectTasks where o.TaskNumber == 457 select o.Project).ToList();

    Yep that returns all the ProjectTasks just like the original issue.

    Got to love it! :-(

    Tuesday, November 24, 2015 3:32 PM
  • Again... you propably didn't read that:

    Now i understand, you're not having trobles with the LINQ, you have troubles understanding, that when you query an Entity and Expand (or Include) the NavigationProperties (Referenced Tables), you cannot have the NavigationProperties filtered as well, cause that would mean an Inconsistent state of an Entity. So if you need to have an Project which only has Task #var# on it, You'll need to create a Project (get it wothout Include or Expand) and then add only the Task you want and then DETATCH that thing from the EF so it does not get tracked and messes everything up.

    • Marked as answer by Peter Viau Tuesday, November 24, 2015 3:43 PM
    Tuesday, November 24, 2015 3:34 PM
  • Thanks for your efforts!
    Tuesday, November 24, 2015 3:44 PM