Join two tables issue in linq

Question

I am developing a web portal for reports and updates.. For displaying records.. I am passing id from controller parameters..

If am fetching data from one table it is working fine.. Eg

Var employee = slp.urlt.where ( x=> x.Id == Id).ToList ();

But when i am joining with another table it gives error

var result = from ut in slp.urlt
                     join ct in slp.Cities on ut.City equals ct.Id
                     where ut.Id == Id
                     select new
                     {
                         ut.R_Name_Enn,
                         ut.R_Name_Arr,
                         ut.R_Addr_Enn,
                         ut.R_Addr_Arr,
                         ct.Id,
                         ct.Name,
                         ct.Name_Arr
                     };

So i was check with sql .. following query working fine in sql ..

SELECT A.R_name_e,A.R_name_a,A.R_addr_e,A.R_addr_a,B.Id,B.Name,B.Name_ar FROM urlt A inner join City B on A.City = B.Id WHERE A.Id = 90000001

Answer 1

Okay, so you have a DbContext with Employees and Cities. Every Employee lives in a City; every City is the residence of zero or more Employees. Clearly a simple one-to-many relation using a foreign key.

Following the entity framework code-first conventions you would have something similar to the following classes:

class City
{
    public int Id {get; set;}

    // every city Houses zero or more Employees:
    public virtual ICollection<Employee> Employees {get; set;}

    ... // other properties
}

class Employee
{
    public int Id {get; set;}

    // every Employee lives in exactly one City, using foreign key
    public int CityId {get; set;}
    public virtual City {get; set;}
}

class MyDbContext : DbContext
{
    public DbSet<City> Cities {get; set;}
    public DbSet<Employee> Employees {get; set;}
}

Because I follow the entity framework code first conventions, entity framework will capable to detect the tables and the columns and the one-to-many relation between the City and the Employees.

Only if you want to use non-default names for tables, or columns, you'll need Attributes or fluent API.

Back to your question

Given an Id you want several properties of the Employee with this Id, inclusive several properties of the City that houses this Employee

You could use a join. However, if you use the City property, then entity framework is smart enough to understand which join is needed. The code looks much more natural to the reader:

var queryEmployees = myDbcontext.Employees   // from the sequence of all Employees
    .Where(employee => employee.Id == Id)    // keep only the employee with this Id
    .Select(employee => new                  // from the remaining employees
    {                                        // make one new object with properties:
        NameEnn = employee.R_Name_Enn,
        NameArr = ut.R_Name_Arr,
        AddrEnn = ut.R_Addr_Enn,
        AddrArr = ut.R_Addr_Arr,            
        City = new                           // I chose to make a sub-property for city
        {                                     // if desired, you can flatten it.
            Id = employee.City.Id,
            Name = employee.City.Name,
            NameArr = employee.City.Name_Arr,
        },
    });

I expect there will only be one employee with this Id. To fetch this one Employee use:

var fetchedEmployee = queryEmployees.FirstOrDefault();

or if you really want a list with this one Employee:

var fetchedEmployees = queryEmployees.ToList();

If you really think that a join is more readable, and better maintainable - which I doubt - You can get the same result using an inner join:

var queryEmployees = myDbcontext.Employees   // from the sequence of all Employees
    .Where(employee => employee.Id == Id)    // keep only the employee with this Id
    .Select(employee => new                  // join the remaining employees
    .Join(myDbcontext.Cities,                // with the sequence of Cities
    employee => employee.CityId,             // from each Employee take the CityId
    city => city.Id                          // from each City take the Id,
    (employee, city) => new                  // when they match 
    {                                        // make one new object with properties:
        NameEnn = employee.R_Name_Enn,
        NameArr = ut.R_Name_Arr,
        AddrEnn = ut.R_Addr_Enn,
        AddrArr = ut.R_Addr_Arr,            
        City = new
        {
            Id = city.Id,
            Name = city.Name,
            NameArr = city.Name_Arr,
        },
    });