Relationship between foreign keys and the LINQ Include opeator

Question

I am struggling with a LINQ query using EF Core. In short, I have four tables linked with foreign keys but cannot seem to write the correct LINQ functions. Here is a diagram of the tables in the database:

What I want to do is create a LINQ query that generates the same data as the following SQL statement:

SELECT [Row],[Col],[TextLines]
FROM dbo.[Models] m
INNER JOIN dbo.[Table] t
ON m.ID=t.ModelID
INNER JOIN dbo.[TableCells] tc
ON t.ID=tc.TableID
INNER JOIN [TableCellsLines] tcl
ON tc.ID=tcl.TableCellId
WHERE m.ModelType='table' AND Part=3 AND Chapter=1 AND Lesson=1
ORDER BY tc.[Row], tc.[Col]

Here are the SQL Statements used to create the Links:

ALTER TABLE dbo.[Table]
ADD CONSTRAINT FK_Table_Model FOREIGN KEY (ModelID) REFERENCES dbo.[Models](ID)

ALTER TABLE dbo.[TableCells]
ADD CONSTRAINT FK_TableCells_Tables FOREIGN KEY(TableID) REFERENCES dbo.[Table](ID)

ALTER TABLE dbo.[TableCellsLines]
ADD CONSTRAINT FK_TableCellsLines_TableCells FOREIGN KEY(TableCellId) REFERENCES dbo.[TableCells](ID)

Now, I was thinking that the constraints would allow me to do something like:

    var Model = TEASPrepContext.Models.Where(m => m.ModelType == ModelType && m.Part == Part &&  m.Chapter == Chapter && m.Lesson == Lesson)
                    .Include(m => m.Tables)
                    .Include(t=>t.TableCells)
                    .Include(tc=>tc.TableCellsLines)
                    .Select( ... )

However, the compiler will not allow the second and third Include calls. Obviously, I do not understand the relationship between foreign keys and LINQ. I would be grateful for anyone who could offer a solution and, just as importantly, a document or tutorial that could explain how LINQ and foreign keys work.

Thank you for your assistance.

Answer 1

Include not for querying but for loading related data. Usually it is not needed at all if you do not load data for modification. Also after Select, GroupBy it is discarded.

Your query is a set of SelectMany operators which is readable only with Query Syntaxt:

 var query =
    from m in TEASPrepContext.Models
    from t in m.Tables
    from tc in t.TableCells
    from tcl in tc.TableCellsLines
    where m.ModelType == ModelType && m.Part == Part && m.Chapter == Chapter && m.Lesson == Lesson
    orderby tc.Row, tc.Col
    select new 
    {
         tc.Row, 
         tc.Col,
         tcl.TextLines
    };