ASP.NET MVC 5 Entity Join

Question

I'm new in ASP, Entity and lambda expressions. How can I join two tables?

Route Model:

public partial class Route
{
    public Route()
    {
        Flights = new HashSet<Flight>();
    }

    public int RouteID { get; set; }

    public int DepartureAirportID { get; set; }

    public int ArrivalAirportID { get; set; }

    public int FlightDuration { get; set; }

    public virtual Airport Airport { get; set; }

    public virtual Airport Airport1 { get; set; }

    public virtual ICollection<Flight> Flights { get; set; }
}

Airport Model:

public partial class Airport
{
    public Airport()
    {
        Routes = new HashSet<Route>();
        Routes1 = new HashSet<Route>();
    }

    public int AirportID { get; set; }

    public string City { get; set; }

    public string Code { get; set; }

    public virtual ICollection<Route> Routes { get; set; }

    public virtual ICollection<Route> Routes1 { get; set; }
}

SQL query looks like this:

SELECT a.AirportID, a.City
FROM Route r INNER JOIN Airport a ON r.ArrivalAirportID = a.AirportID
WHERE r.DepartureAirportID = @departureAirportID
ORDER BY a.City

Sorry for this easy question but I don't know how to do this with Entity Framework...

Ric · Accepted Answer · 2015-06-22 10:06:06Z

2

Something like this should do (untested and just going on from your query) with a variable hard-coded):

using (var db = new YourDbContext())
{
    var query = from r in db.Route
                join a in db.Airport a on r.ArrivalAirportID equals a.AirportID
                where r.DepartureAirportID = 1 // replace with your varialble.
                orderby a.City
                select a;
}

answered Jun 22, 2015 at 10:06

Ric

13.3k3 gold badges32 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rajesh Kumar · Accepted Answer · 2015-10-21 05:49:08Z

Include with join entity framework. here doctorSendAnswerModel also a inner table.

 var data = _patientaskquestionRepository.Table.Include(x=>x.DoctorSendAnswer).Join(_patientRepository.Table, a => a.PatientId, d => d.Id, (a, d) => new { d = d, a = a }).Where(x => x.a.DoctorId == doctorid);
         if(!string.IsNullOrEmpty(status))
          data=data.Where(x=>x.a.Status==status);
          var result = data.Select(x => new {x= x.a,y=x.d }).ToList();
          var dt = result.Select(x => new PatientAskQuestionModel()
          {
              PatientId = x.x.PatientId.Value,
              AskQuestion = x.x.AskQuestion,
              Id = x.x.Id,
              DoctorId = x.x.DoctorId,
              FileAttachment1Url = x.x.FileAttachment1,
              DocName = x.y.FirstName + " " + x.y.LastName,
              CreatedDate = x.x.CreatedDate.Value,
              doctorSendAnswerModel = x.x.DoctorSendAnswer.Select(t => new DoctorSendAnswerModel { Answer = t.Answer }).ToList()
          }).ToList();


          return dt;

DevT · Accepted Answer · 2015-06-22 10:05:17Z

1

LinQ query:

from r in context.Route
join  a in context.Airport 
on r.ArrivalAirportID equals a.AirportID
WHERE r.DepartureAirportID = "value"
ORDER BY a.City
select a.AirportID, a.City

answered Jun 22, 2015 at 10:05

DevT

4,95316 gold badges63 silver badges95 bronze badges

Comments

Neel · Accepted Answer · 2015-06-22 10:05:51Z

1

var balance = (from a in context.Airport 
               join c in context.Route on a.ArrivalAirportID equals c.AirportID
               where c.DepartureAirportID == @departureAirportID
               select a.AirportID)
              .SingleOrDefault();

answered Jun 22, 2015 at 10:05

Neel

11.7k3 gold badges46 silver badges63 bronze badges

Comments

Mathew Thompson · Accepted Answer · 2015-06-22 10:07:16Z

1

You can do the following:

var matches = from a in context.Airports
              join r in context.Routes 
                  on a.AirportID equals r.ArrivalAirportID
              where r.DepartureAirportID = departureAirportID
              order by a.City
              select new
              {
                  a.AirportID,
                  a.City
              };

answered Jun 22, 2015 at 10:07

Mathew Thompson

56.5k15 gold badges131 silver badges151 bronze badges

Comments

Rajesh Kumar · Accepted Answer · 2015-10-21 05:43:25Z

Entity query with conditional join with pagination.

  if (pageIndex <= 0)

            pageIndex = 1;

        pageIndex = ((pageIndex - 1) * pageSize) ;

        var patient = _patientRepository.Table.Join(_DoctorPatient.Table.Where(x => x.DoctorId == Id && x.IsBlocked==false), x => x.Id, d => d.PatientId, (x, d) => new { x = x });

        if (state != "")
            patient = patient.Where(x => x.x.State.Contains(state));
        if (name != "")
            patient = patient.Where(x => (x.x.FirstName + x.x.LastName).Contains(name));


        if (sdate != null)
            patient = patient.Where(x => x.x.CreatedDate >= sdate);
        if (eDate != null)
            patient = patient.Where(x => x.x.CreatedDate <= eDate);

        var result = patient.Select(x => x.x).Select(x => new PatientDoctorVM() { PatientId = x.Id, Id = x.Id, FirstName = x.FirstName, LastName = x.LastName, SSN = x.NewSSNNo, UserProfileId = x.UserProfileId, Email = x.Email, TumbImagePath = x.TumbImagePath }).OrderBy(x => x.Id).Skip(pageIndex).Take(pageSize).ToList();

Dinesh.net · Accepted Answer · 2016-09-20 12:40:27Z

1

Your entity and lembda query will be lool like this:

  return (from d in _doctorRepository.Table 
       join p in _patientDoctor.Table on d.Id equals p.DoctorId 
      where p.PatientId == patientid.Value select d
       ).ToList();

answered Sep 20, 2016 at 12:40

Dinesh.net

791 silver badge9 bronze badges

Comments

Sibster · Accepted Answer · 2015-06-22 10:09:38Z

0

Take a look at this site, it will explain you how the join works in Linq. So if you ever need it again you will be able to solve it yourself.

answered Jun 22, 2015 at 10:09

Sibster

3,2191 gold badge24 silver badges21 bronze badges

Collectives™ on Stack Overflow

ASP.NET MVC 5 Entity Join

8 Answers 8

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related