I am trying to create a linq query with 3 tables involved. The main table (A) has 2 fields that are pointing to the same table (C) from which I need to return Full Name (FirstName LastName) for each of the two fields. In addition I need to return a third Full Name (FirstName LastName) from table (C) using a middle table (B) that has a common field with A (taxID), how can I create a linq query that does that at once please? Any help is appreciated. I am using Entity Framework

        public List<SiteVisitsScheduleDTO> GetAllTEST(int selectedTaxID, bool completedVisits)
    {
        List<SiteVisitsScheduleDTO> ObjSiteVisitsList = new List<SiteVisitsScheduleDTO>();
        try
        {
            var SVQ = from schedules in ObjContext.SiteVisitsSchedules.AsQueryable()
                      join managers1 in ObjContext.NetworkManagers.AsQueryable() on schedules.ScheduledBy equals managers1.ManagerID
                      join managers2 in ObjContext.NetworkManagers.AsQueryable() on schedules.CompletedBy equals managers2.ManagerID
                      join crosswalk in ObjContext.ProviderManagerCrosswalks.AsQueryable() on schedules.TaxID equals crosswalk.TaxID
                      join managers3 in ObjContext.NetworkManagers.AsQueryable() on crosswalk.ManagerID equals managers3.ManagerID
                      select new
                      {
                          schedules.SiteVisitID,
                          schedules.TaxID,
                          schedules.ScheduledBy,
                          schedules.CompletedBy,
                          **??????**
                      };

            foreach (var item in SVQ)
            {
                ObjSiteVisitsList.Add(new SiteVisitsScheduleDTO
                {
                    SiteVisitID = item.SiteVisitID,
                    TaxID = item.TaxID,
                    ScheduledBy = item.ScheduledBy,
                    CompletedBy = item.CompletedBy,
                });
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        return ObjSiteVisitsList;
    }

I am attaching an image to explain

thanks in advance

CodePudding user response：

Alas you forgot to tell us how you access your database. Entity Framework? Or any other method?

Anyway, apparently you have three tables, filled with rows from type A / Tax / Manager respectively. You will have classes similar to:

class Manager
{
    public int Id {get; set;}                    // primary key
    public string FirstName {get; set;}
    public string LastName {get; set;}
}

Every Tax (in table B) has a primary key in TaxId, zero or more other properties and a foreign key to one of the Managers in table C.

class Tax
{
    public int TaxId {get; set;}                    // primary key

    // foreign key of the one and only manager of this B:
    public int ManagerId {get; set;}
}

If you are using entity framework, class B will also have a virtual property that holds the one and only Manager of this B (= the Manager that foreign key ManagerId refers to):

    public virtual Manager Manger {get; set;}   // in case of entity framework

Every row in table A has a primary key, zero or more other properties, and three foreign keys: two foreign keys to Managers in table C, one in property SchManagerId and one in property ComManagerId, and one foreign key to a Tax in table B in property TaxId:

class A
{
    public int Id {get; set;}                    // primary key

    // two foreign key to the Managers of this A
    public int SchManagerId {get; set;}
    public int ComManagerId {get; set;}
    
    // one foreign key to a Tax:
    public int TaxId {get; set;}
}

And similar to class Tax: if you use entity framework, class A will also have virtual properties to the objects that the foreign keys refer to:

   public virtual Manager SchManager {get; set;}
   public virtual Manager ComManager {get; set;}
   public virtual Tax Tax {get; set;}

You will also have access to the tables. This is done in properties that return IQueryable<...>. If you use entity framework this will be in public DbSet<Manager> Managers {get; set;}, and something similar for Taxes and rows from table A

public IQueryable<Manager> Managers => ...
public IQueryable<Tax> Taxes => ...
public IQueryable<A> TableARows => ...

You might have used other identifiers. Consider to give us more information about your classes in your next question. It will make everything I typed until now not needed.

Requirement: For every Row in table A, give me the first and last name of the SchManager and the ComManager that the foreign keys refer to. Also give me the first and last name of the Manager that belongs to the Tax that foreign key TaxId refers to.

var result = TableARows.Select(tableARow => new
{
    // Select names of the one and only manager that SchManagerId refers to:
    SchManager = Managers
        .Where(manager => manager.Id == tableARow.SchManagerId)
        .Select(manager => new
        {
            FirstName = manager.FirstName,
            LastName = manager.LastName,
        })
        .FirstOrDefault(),

    // Select names of the one and only manager that CommManagerId refers to:
    ComManager = Managers
        .Where(manager => manager.Id == tableARow.ComManagerId)
        .Select(manager => new
        {
            FirstName = manager.FirstName,
            LastName = manager.LastName,
        })
        .FirstOrDefault(),

    // this tableARow has exactly one Tax, which has exactly one Manager
    TaxManager = Taxes
        .Where(tax => tax.Id == manager.TaxId)
        .SelectMany(tax => Managers.Where(manager.Id == tax.ManagerId))
        .Select(manager => new
        {
            FirstName = manager.FirstName,
            LastName = manager.LastName,
        })
        .FirstOrDefault(),
});

SchManager and ComManager are fairly straight forward. The SelectMany in TaxManager might seem strange: we know that each tableARow has exaclty one Tax, which has exactly one TaxManager, so why SelectMany?

This is for efficiency. We want to scan the Manager table only once. So for every tableARow, we keep only those Taxes that have a primary key that the foreign key TaxId refers to. From every remaining Tax (of which we know there is only one, but officially there might be more), we get "all its Managers".

Whenever you have a sequence of items, where you know that every item has zero or more subitems, and you only want to look at the subItems as if it is one array, you need to use SelectMany.

Item X has subitems 01, 02, 03
Item Y has subitems 12, 13, 14
Item Z has subitems 24, 23, 22

If you are not interested in the Items, but only in the subitems, then after SelectMany you will have

subItems 01, 02, 03, 12, 13, 14, 24, 23, 22

So after SelectMany of the Taxes of the tableARow and all Managers of these Taxes, we have a sequence of Managers. We can use an extra select to fetch only the first and last name of these Managers. We know the tableARow has only one Tax, which has only one Manager, so we only need the FirstOrDefault