Series 2 simple query and join query

This article mainly summarizes the simple query (single-Table query) and join query (Multi-Table query) of LINQ to SQL)

Single Table query

The requirement is that we need to output the results in the tclass table. Use from... In... Select statement, the Code is as follows:

public static void SimpleQuery()        {            using (L2SDBDataContext db = new L2SDBDataContext())            {                var query = from tc in db.TClasses                            //select tc;                            select new {                                 ClassID=tc.ClassID,                                ClassName=tc.ClassName                            };                Console.WriteLine("output results for table of class");                int i = 1;                foreach (var item in query)                {                    Console.WriteLine("{0},ClassID:{1},ClassName:{2}",i,item.ClassID,item.ClassName);                    i++;                }            }        }

Note: Select TC is not used here, but a new anonymous type is defined because of performance considerations, which will be discussed later in the performance optimization section.

Output result:

Multi-Table query

Multi-table queries can also be called join queries. You need to use a foreign key to join multiple tables to query the expected results. The current requirement is the information of a class and related students of this class. There are two ways to implement this requirement. One is inner join, and the other is outer join. The following is the code for querying through inner join.

public static void Query_InnerJoin()        {            using (L2SDBDataContext db = new L2SDBDataContext())            {                var query = from s in db.TStudents                            join c in db.TClasses on s.ClassID equals c.ClassID                            where s.ClassID == 3                            select new                            {                                ClassID = s.ClassID,                                ClassName = c.ClassName,                                Student = new                                {                                    Name = s.Name,                                    StudentID = s.StudentID                                }                            };                foreach (var item in query)                {                    Console.WriteLine("{0} {1} {2}", item.ClassID, item.ClassName, item.Student.Name);                }                }        }

Running result:

Outer Join code:

public static void Query_OutJoin()        {            using (L2SDBDataContext db = new L2SDBDataContext())            {                var query = from s in db.TStudents                            join c in db.TClasses on s.ClassID equals c.ClassID into gc                            from gci in gc.DefaultIfEmpty()                            where s.ClassID == 3                            select new                            {                                ClassID = s.ClassID,                                ClassName = gci.ClassName,                                Student = new                                {                                    Name = s.Name,                                    StudentID = s.StudentID                                }                            };                foreach (var item in query)                {                    Console.WriteLine("{0}, {1} [{2}]", item.ClassID, item.ClassName, item.Student.Name);                }            }        }

Note: During outer join, the joined table must be into a new variable and the defaultifempty method of this object must be called.

The running result is the same as that of the inner join.