I have the following tables
I want to retrieve all the information on student==1
In sql I would do something like BELOW and get all the info about a student.
select * from Student s join StudentClass sc on s.StudentID=sc.StudentID join ClassRoom c on sc.ClassID=c.ClassID left join StudentDescription sd on s.StudentID=sd.StudentID where s.StudentID=14
Now my problem.Using EF4 I did something like this but cannot make it work. Also can you do an include and a left join
Attempt 1
private static StudentDto LoadStudent(int studentId) { StudentDto studentDto = null; using (var ctx = new TrainingContext()) { var query = ctx.Students .Include("ClassRooms") .Include("StudentDescriptions") .Where(x=>x.StudentID==studentId) .SingleOrDefault(); studentDto = new StudentDto(); studentDto.StudentId = query.StudentID; studentDto.StudentName = query.StudentName; studentDto.StudentDescription = ?? } return studentDto; }
Attempt 2 again incomplete and wrong
using (var ctx = new TrainingContext()) { var query = (from s in ctx.Students .Include("ClassRooms") join sd in ctx.StudentDescriptions on s.StudentID equals sd.StudentID into g from stuDesc in g.DefaultIfEmpty() select new { Name=s.StudentName, StudentId=s.StudentID, }).SingleOrDefault();
As you can see I dont know what I am doing here. How can I convert that Sql into a EF Query?
You can use LINQ to perform a left outer join by calling the DefaultIfEmpty method on the results of a group join.
There is not a "better" or a "worse" join type. They have different meaning and they must be used depending on it. In your case, you probably do not have employees with no work_log (no rows in that table), so LEFT JOIN and JOIN will be equivalent in results.
Left joins can increase the number of rows in the left table if there are multiple matches in the right table.
A left outer join is a method of combining tables. The result includes unmatched rows from only the table that is specified before the LEFT OUTER JOIN clause. If you are joining two tables and want the result set to include unmatched rows from only one table, use a LEFT OUTER JOIN clause or a RIGHT OUTER JOIN clause.
Yes, it is possible.
Firstly, .Include
does a LEFT OUTER JOIN, using the navigational property you pass through.
This is how you would explicitly do a LEFT JOIN between Student and StudentDescription:
var query = from s in ctx.Students from sd in s.StudentDescriptions.DefaultIfEmpty() select new { StudentName = s.Name, StudentDescription = sd.Description };
As you can see, it's performing the JOIN based on the entity association between Students and StudentDescriptions. In your EF model, you should have a navigational property called StudentDescriptions on your Student entity. The above code is simply using that to perform the join, and defaulting if empty.
The code is basically identical to .Include
.
Please don't get confused with LEFT JOIN vs LEFT OUTER JOIN.
They are the same thing.
The "OUTER" keyword is optional, i believe it is there for ANSI-92 compatability.
Just .Include
everything you need in your query:
using (var ctx = new TrainingContext()) { studentDo = ctx.Students .Include("ClassRooms") .Include("StudentDescriptions") .Where(x=>x.StudentID==studentId) .Select(x => new StudentDto { StudentId = x.StudentId, StudentName = x.StudentName StudentDescription = x.StudentDescription.Description }) .SingleOrDefault(); }
Basically, make sure all your FK's are expressed as navigational properties on your model, then if so, you don't need to do any joins. Any relationships you require can be done with .Include
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With