none
Linq to entities Query RRS feed

  • Question

  • hi im a bit new to Linq To Entities. 
    i have this query. im using LinqPad to run this:
    int employeeId = 6;
            var endo = (from da in iimsDb.DepartmentAssignments
           where da.Employee2.EmployeeId == employeeId
           || da.Employee.EmployeeId == employeeId
           || da.Employee1.EmployeeId == employeeId
           || da.TopLevelAssignment.Employee1.EmployeeId == employeeId
           || da.TopLevelAssignment.Employee2.EmployeeId == employeeId
           || da.TopLevelAssignment.Employee3.EmployeeId == employeeId
           || da.TopLevelAssignment.Employee.EmployeeId == employeeId
           select da.TopLevelAssignment.EndorsementSubject.Endorsement).Distinct();
            endo.Dump();
    im the selecting the Endorsement Entity here. Endorsement has a relationship to Account Entity. I want to get the Account data.
    I've tried using .Include("Account"). but it doesnt seem to work. need some advice. tnx.
    Thursday, March 31, 2011 6:24 AM

Answers

  • Hi,

    Hmm.. about the first one, it's difficult to say why it didn't work. It is the way to retrieve sub navigation with eager loading. I have read your code again, and can't see why it didn't work. Anyway, lazy loading should load your Account navigation property, if that doesn't load it, something else is wrong (Try to call endo.Account and it should be filled automatically on lazy loading).

    As for your second query here, there is actually several ways to solve this. A problem is that I don't know the navigation property of your tables, so it is a bit difficult to say exactly how. But something like this should work:

    var result = context.Person.Include("Horoscope").Where(row => row.PersonLocation.Where(row2 => row2.Country.CountryName == "countryname").Any()).Distinct();

    There is some assumptions here.
    1. The Horoscope navigation property on Person is named Horoscope
    2. The PersonLocation navigation property on Person is named PersonLocation and is a collection object

    Hope this helps,


    --Rune
    • Marked as answer by marxman Friday, April 1, 2011 5:44 AM
    Thursday, March 31, 2011 5:31 PM

All replies

  • Hi!

    You need to include the whole path to the Account entity.

    For you it should be something like this:

    iimsDb.DepartmentAssignments.Include("TopLevelAssignment.EndorsementSubject.Endorsement.Account")

    You should then get your account object.

    But, another option is that if you have enabled Lazy loading, endo.Account should also lazy load your account object.

    Hope this helps!


    --Rune
    Thursday, March 31, 2011 6:30 AM
  • Hi Rune,

    thanks for the reply. I've tried your advice. but it still doesn't show the Account.

    could it be because of this select da.TopLevelAssignment.EndorsementSubject.Endorsement

     

    i have this kind of query coz i was trying to get the distinct endorsements.


    i've tried this simple set of tables. i have the ff:

    Person

    PersonId

    HoroscopeId

    FullName

    -----------

    PersonLocation

    PersonLocationId

    PersonId

    CountryId

    Address

    ----------

    Horoscope

    HoroscopeId

    HoroscopeName

    ----------

    Country

    CountryId

    CountryName

     

    now my goal here is to get all distinct Person who is living in a certain Country. and included on the Person data is the Horoscope Entity.

     

    what should be the best approach or query for this?


    Thursday, March 31, 2011 7:53 AM
  • Hi,

    Hmm.. about the first one, it's difficult to say why it didn't work. It is the way to retrieve sub navigation with eager loading. I have read your code again, and can't see why it didn't work. Anyway, lazy loading should load your Account navigation property, if that doesn't load it, something else is wrong (Try to call endo.Account and it should be filled automatically on lazy loading).

    As for your second query here, there is actually several ways to solve this. A problem is that I don't know the navigation property of your tables, so it is a bit difficult to say exactly how. But something like this should work:

    var result = context.Person.Include("Horoscope").Where(row => row.PersonLocation.Where(row2 => row2.Country.CountryName == "countryname").Any()).Distinct();

    There is some assumptions here.
    1. The Horoscope navigation property on Person is named Horoscope
    2. The PersonLocation navigation property on Person is named PersonLocation and is a collection object

    Hope this helps,


    --Rune
    • Marked as answer by marxman Friday, April 1, 2011 5:44 AM
    Thursday, March 31, 2011 5:31 PM
  • hi Rune,

    thanks. you've been very helpful. but still it's not working. can i send you an image file of the navigation properties? not really sure how to send it to you. u have an email? or is there a way i can attach it here?

    Friday, April 1, 2011 5:30 AM
  • hi Rune, 

    sorry bout that, omitted some methods there. but its working now. thanks much. will try to apply it. 

    you've been very helpful.

    Friday, April 1, 2011 5:43 AM
  • Hi,

    Good to hear you solved it!


    --Rune
    Friday, April 1, 2011 5:51 AM
  • hi Rune,
    thanks much! was able to apply it to my current query.
    here's my code:
    var result = iimsDb.Endorsements
                    /*.Include("Branch")
                    .Include("Division")
                    .Include("Service")
                    .Include("Account")
                    .Include("ClientSite")
                    .Include("Contact")
                    .Include("ReportFormat")*/
            .Where(row => row.EndorsementSubjects
                .Where(row2 => row2.TopLevelAssignments
                    .Where(row3 =>
                        row3.Employee.EmployeeId == 6
                        || row3.Employee1.EmployeeId == 6
                        || row3.Employee2.EmployeeId == 6
                        || row3.Employee3.EmployeeId == 6).Any()).Any()).Distinct();
    var result1 = iimsDb.Endorsements
                    /*.Include("Branch")
                    .Include("Division")
                    .Include("Service")
                    .Include("Account")
                    .Include("ClientSite")
                    .Include("Contact")
                    .Include("ReportFormat")*/
                    .Where(row => row.EndorsementSubjects
                        .Where(row2 => row2.TopLevelAssignments
                            .Where(row3 => row3.DepartmentAssignments.Where(row4 =>
                                row4.Employee.EmployeeId == 6
                                || row4.Employee1.EmployeeId == 6
                                || row4.Employee2.EmployeeId == 6).Any()).Any()).Any()).Distinct();
    var union = result.Union(result1).Distinct();
    //union.Dump();



    Friday, April 1, 2011 7:56 AM
  • made a much simpler query..
    var endorsements = Endorsements
    .Include("Branch")
    .Include("Division")
    .Include("Service")
    .Include("Account")
    .Include("ClientSite")
    .Include("Contact")
    .Include("ReportFormat")
    .Where(rowa=>rowa.EndorsementSubjects.Where(row => row.TopLevelAssignments.Where(
    row2 => row2.Employee.EmployeeId == 6
    || row2.Employee1.EmployeeId == 6
    || row2.Employee2.EmployeeId == 6
    || row2.Employee3.EmployeeId == 6).Any()
    ||
    row.TopLevelAssignments.Where(row3 => row3.DepartmentAssignments.Where(
    row4 => row4.Employee.EmployeeId == 6
    || row4.Employee1.EmployeeId == 6
    || row4.Employee2.EmployeeId == 6
    ).Any()).Any()).Any());
    endorsements.Dump();
    Saturday, April 2, 2011 1:52 AM