EFCore-SQL mapper

INCLUDE

Select all student Info,subjectIds , what studentId = 2

var lst = context.Student.Where(p => p.Id == 2)

         .Include(p => p.StudentSubjects) 

          .ToList();

01) first execute the query to read particular student

SELECT [p].[Id], [p].[Birthday], [p].[Name]
FROM [Student] AS [p]
WHERE [p].[Id] = 2
ORDER BY [p].[Id]

02) Second, execute the query for read studentSubject

SELECT [p.StudentSubjects].[StudentId], [p.StudentSubjects].[SubjectId], [p.StudentSubjects].[AdmissionFrom]
FROM [StudentSubject] AS [p.StudentSubjects]
INNER JOIN (
    SELECT [p0].[Id]
    FROM [Student] AS [p0]
    WHERE [p0].[Id] = 2
) AS [t] ON [p.StudentSubjects].[StudentId] = [t].[Id]
ORDER BY [t].[Id]

two separate SQL will generate and execute for two tables.

====================================================
Select all Student info,subjectIds and phone number what student id == 2

      var lst = context.Student.Where(p => p.Id == 2)
                    .Include(p => p.StudentSubjects)
                    .Include(p=>p.PhoneNumbers)
                    .ToList();
01) first execute the query to read particular student

SELECT [p].[Id], [p].[Birthday], [p].[Name]
FROM [Student] AS [p]
WHERE [p].[Id] = 2
ORDER BY [p].[Id]

02) Second, execute the query for read studentSubject

SELECT [p.StudentSubjects].[StudentId], [p.StudentSubjects].[SubjectId], [p.StudentSubjects].[AdmissionFrom]
FROM [StudentSubject] AS [p.StudentSubjects]
INNER JOIN (
    SELECT [p0].[Id]
    FROM [Student] AS [p0]
    WHERE [p0].[Id] = 2
) AS [t] ON [p.StudentSubjects].[StudentId] = [t].[Id]
ORDER BY [t].[Id]

02) Second, execute the query for reading Student phone numbers

SELECT [p.PhoneNumbers].[StudentId], [p.PhoneNumbers].[PhoneNumber]
FROM [PhoneNumbers] AS [p.PhoneNumbers]
INNER JOIN (
    SELECT [p1].[Id]
    FROM [Student] AS [p1]
    WHERE [p1].[Id] = 2
) AS [t0] ON [p.PhoneNumbers].[StudentId] = [t0].[Id]
ORDER BY [t0].[Id]

three separate SQL will generate and execute for 3 tables

===========================================

03) ThenInclude

 Select Student info and Subject what student id 2 has

 var lst = context.Student.Where(p => p.Id == 2)
                    .Include(p => p.StudentSubjects)
                    .ThenInclude(p=>p.Subject)
                    .ToList();

01) first execute the query to read particular student

SELECT [p].[Id], [p].[Birthday], [p].[Name]
FROM [Student] AS [p]
WHERE [p].[Id] = 2
ORDER BY [p].[Id]

02) Second, execute the query for read studentSubject and subjects

SELECT [p.StudentSubjects].[StudentId], [p.StudentSubjects].[SubjectId], [p.StudentSubjects].[AdmissionFrom], [s.Subject].[Id], [s.Subject].[Name]
FROM [StudentSubject] AS [p.StudentSubjects]
INNER JOIN [Subject] AS [s.Subject] ON [p.StudentSubjects].[SubjectId] = [s.Subject].[Id]
INNER JOIN (
    SELECT [p0].[Id]
    FROM [Student] AS [p0]
    WHERE [p0].[Id] = 2
) AS [t] ON [p.StudentSubjects].[StudentId] = [t].[Id]

ORDER BY [t].[Id]

(ThenInclude query makes inner join query)

====================================

Select all subject teaches what student id==2

var lst = context.Student.Where(p => p.Id == 2)
                    .Include(p => p.StudentSubjects)
                    .ThenInclude(p=>p.Subject.Teachers)
                    .ToList();
01) select student who has id 2 

SELECT [p].[Id], [p].[Birthday], [p].[Name]
FROM [Student] AS [p]
WHERE [p].[Id] = 2
ORDER BY [p].[Id]

02) select studentsubject inner join subject 

SELECT [p.StudentSubjects].[StudentId], [p.StudentSubjects].[SubjectId], [p.StudentSubjects].[AdmissionFrom], [s.Subject].[Id], [s.Subject].[Name]
FROM [StudentSubject] AS [p.StudentSubjects]
INNER JOIN [Subject] AS [s.Subject] ON [p.StudentSubjects].[SubjectId] = [s.Subject].[Id]
INNER JOIN (
    SELECT [p0].[Id]
    FROM [Student] AS [p0]
    WHERE [p0].[Id] = 2
) AS [t] ON [p.StudentSubjects].[StudentId] = [t].[Id]

ORDER BY [t].[Id], [s.Subject].[Id]

02) select teaches 

SELECT [p.StudentSubjects].[StudentId], [p.StudentSubjects].[SubjectId], [p.StudentSubjects].[AdmissionFrom], [s.Subject].[Id], [s.Subject].[Name]
FROM [StudentSubject] AS [p.StudentSubjects]
INNER JOIN [Subject] AS [s.Subject] ON [p.StudentSubjects].[SubjectId] = [s.Subject].[Id]
INNER JOIN (
    SELECT [p0].[Id]
    FROM [Student] AS [p0]
    WHERE [p0].[Id] = 2
) AS [t] ON [p.StudentSubjects].[StudentId] = [t].[Id]

ORDER BY [t].[Id], [s.Subject].[Id]


========================================
Select student name and teacher name only

result
SELECT [student].[Name] AS [Name0], [subject].[Name] AS [SubjectName]
FROM [Student] AS [student]
INNER JOIN [StudentSubject] AS [studentSubject] ON [student].[Id] = [studentSubject].[StudentId]
INNER JOIN [Subject] AS [subject] ON [studentSubject].[SubjectId] = [subject].[Id]
WHERE [student].[Id] = 2
===============================================================

Include + Conditions

Select phone numbers start with 033 who has id 2



========================================
multiple include + then include

  var lst = context.Student.Where(p => p.Id == 2)
                    .Include(p => p.StudentSubjects)
                    .ThenInclude(p => p.Subject)
                    .Include(p => p.StudentSubjects)
                    .ThenInclude(p => p.Teacher).FirstOrDefault();


1st
SELECT TOP(1) [p].[Id], [p].[Birthday], [p].[Name]
FROM [Student] AS [p]
WHERE [p].[Id] = 2
ORDER BY [p].[Id]

2nd 

SELECT [p.StudentSubjects].[StudentId], [p.StudentSubjects].[SubjectId], [p.StudentSubjects].[AdmissionFrom], [p.StudentSubjects].[TeacherId], [s.Teacher].[Id], [s.Teacher].[Name], [s.Teacher].[SubjectId], [s.Subject].[Id], [s.Subject].[Location], [s.Subject].[Name]
FROM [StudentSubject] AS [p.StudentSubjects]
INNER JOIN [Teachers] AS [s.Teacher] ON [p.StudentSubjects].[TeacherId] = [s.Teacher].[Id]
INNER JOIN [Subject] AS [s.Subject] ON [p.StudentSubjects].[SubjectId] = [s.Subject].[Id]
INNER JOIN (
    SELECT TOP(1) [p0].[Id]
    FROM [Student] AS [p0]
    WHERE [p0].[Id] = 2
    ORDER BY [p0].[Id]
) AS [t] ON [p.StudentSubjects].[StudentId] = [t].[Id]

ORDER BY [t].[Id]


========================================
update single colomn

using (var c = new StudentDbContext())
                {
                    var student = c.Student.First();
                    student.Name = "Saman";
                    c.SaveChanges();

                } 
Select query
SELECT TOP(1) [s].[Id], [s].[Birthday], [s].[Name]
FROM [Student] AS [s]

Update query
exec sp_executesql N'SET NOCOUNT ON;
UPDATE [Student] SET [Name] = @p0
WHERE [Id] = @p1;
SELECT @@ROWCOUNT;


',N'@p1 int,@p0 nvarchar(4000)',@p1=2,@p0=N'Saman'

========================================Update with same value (Saman is already there.change only dob)

   using (var c = new StudentDbContext())
  {
   var student = c.Student.First();
    student.Name = "Saman";
    student.Birthday = new DateTime(1900,1,1);
    c.SaveChanges();
  } 
exec sp_executesql N'SET NOCOUNT ON;
UPDATE [Student] SET [Birthday] = @p0
WHERE [Id] = @p1;
SELECT @@ROWCOUNT;


',N'@p1 int,@p0 datetime2(7)',@p1=2,@p0='1900-01-01 00:00:00'

==================================================================

If nothing to update

using (var c = new StudentDbContext())
                {
                    var student = c.Student.First();
                    student.Name = "Saman";
                    student.Birthday = new DateTime(1900,1,1);
                    c.SaveChanges();

                } 
both database has same name what ef send.
then on update query execute.Then all the update query use tracking colomn data

=====================================

Update aggregate root and some aggregate 

  using (var c = new StudentDbContext())
                {
                    var student = c.Subject.Include(p => p.Teachers).First();
                    student.Name = "Commace";
                    student.Teachers[0].Name = "janson";
                    c.SaveChanges();

                } 


Load first subject
SELECT TOP(1) [p].[Id], [p].[Location], [p].[Name]
FROM [Subject] AS [p]

ORDER BY [p].[Id]

Load all teachers to that subject
SELECT [p.Teachers].[Id], [p.Teachers].[Name], [p.Teachers].[SubjectId]
FROM [Teachers] AS [p.Teachers]
INNER JOIN (
    SELECT TOP(1) [p0].[Id]
    FROM [Subject] AS [p0]
    ORDER BY [p0].[Id]
) AS [t] ON [p.Teachers].[SubjectId] = [t].[Id]

ORDER BY [t].[Id]

update subject
exec sp_executesql N'SET NOCOUNT ON;
UPDATE [Subject] SET [Name] = @p0
WHERE [Id] = @p1;
SELECT @@ROWCOUNT;
',N'@p1 int,@p0 nvarchar(4000)',@p1=1,@p0=N'Commace'

update teacher
exec sp_executesql N'SET NOCOUNT ON;
UPDATE [Teachers] SET [Name] = @p0
WHERE [Id] = @p1;
SELECT @@ROWCOUNT;

',N'@p1 int,@p0 nvarchar(4000)',@p1=1,@p0=N'janson'