[10 days to learn about Linq to SQL] The third day -- Data Query

Directory

[10 days to learn about Linq to SQL] day 1-Basic Knowledge [10 days to learn about Linq to SQL] The next day -- data context [10 days to learn about Linq to SQL] The third day -- Data Query

[10 days to learn about Linq to SQL] the fourth day -- Data Update

[10 days to learn about Linq to SQL] day 6-Stored Procedure

 

 

Prepare activities

Before querying data, we must have data, which involves the database. Below is a simple database design;

The SQL statement used to generate a data table is as follows:

    /*==============================================================*//* DBMS name:      Microsoft SQL Server 2005                    *//* Created on:     2013/5/23 14:10:24                           *//*==============================================================*/    if exists (select 1   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')   where r.fkeyid = object_id('Students') and o.name = 'FK_STUDENTS_REFERENCE_CLASSES')alter table Students   drop constraint FK_STUDENTS_REFERENCE_CLASSESgo  if exists (select 1            from  sysobjects           where  id = object_id('Classes')            and   type = 'U')   drop table Classesgo  if exists (select 1            from  sysobjects           where  id = object_id('Students')            and   type = 'U')   drop table Studentsgo  /*==============================================================*//* Table: Classes                                               *//*==============================================================*/create table Classes (   ClassID              int                  identity,   ClassName            char(10)             null,   constraint PK_CLASSES primary key (ClassID))go  /*==============================================================*//* Table: Students                                              *//*==============================================================*/create table Students (   StudentID            int                  identity,   ClassID              int                  null,   StudentName          varchar(20)          null,   StudentNo            varchar(18)          null,   Sex                  int                  null,   Birthday             datetime             null,   Address              varchar(50)          null,   Age                  int                  null,   constraint PK_STUDENTS primary key (StudentID))go  alter table Students   add constraint FK_STUDENTS_REFERENCE_CLASSES foreign key (ClassID)      references Classes (ClassID)go

The database has been created, and now we start to create a Linq to SQL class;

Then, create a database connection from the server resource manager,

Drag the table you need to the container;

Then, save the settings and click OK;

 

Query preparation

 

To try to use Linq to SQL, you must first instantiate the database context DataContext;

 

// Instantiate the data context Entity. studentdatacontext db = new Entity. studentdatacontext ();

 

 

 

Simple query implementation

First, define the anonymous variable var student

Then assign a value to him (the result returned by the data query );

Call the Students table in the data context and try the Where condition query;

// Query the var student = db record where the gender of the student is male (0. students. where (n => n. sex = 0); // output the Student name Console. writeLine ("_____________ simple query __________________________________"); Console. writeLine ("Name of male output student gender:"); foreach (var stu in student) {Console. writeLine (stu. studentName );}

Output result:

 

Query partial Fields

Data must be obtained before a field is returned, so the code is as follows:

    db.Students.Where(n => n.Sex == 0)

After obtaining the data, you can query the fields to be returned.

 

    .Select(a => new { a.StudentName, a.StudentNo }

 

Then return List;

Complete code:

Console. writeLine ("__________ querying partial field queries (querying StudentName and StudentNo) _____________"); var student2 = db. students. where (n => n. sex = 0 ). select (a => new {. studentName,. studentNo }). toList (); foreach (var stu in student2) {Console. writeLine (stu. studentName + "" + stu. studentNo );}

Output result:

Query a single record

 

Use the FirstOrDefault method to query a single record;

The following describes the Student name "CoolHots" records;

    var student3 = db.Students.FirstOrDefault(c => c.StudentName == "CoolHots");Console.WriteLine(student3.StudentName + "  " + student3.StudentNo + "  " + student3.StudentID);

Output result:

 

 

 

Left join query

 

 

Left-side queries are frequently used during development, and the writing of linq is simple and convenient;

The first step of left-side query is similar to a simple query. The second step is to use the Select method to query the fields to be returned, and then compare the query data with the fields associated with other tables;

The Code is as follows:

 

    var student4 = db.Students.Where(n => n.Sex == 0)    .Select(m => new        {            m.StudentID,            m.StudentName,            m.StudentNo,            ClassName = db.Classes.FirstOrDefault(a => a.ClassID == m.ClassID).ClassName        }        )        .ToList();  foreach (var stu in student4){    Console.WriteLine(stu.StudentName + " " + stu.ClassName);}

Result:

 

 

 

 

Complete project code:

 

Public static void Main (string [] args) {// instantiate the data context Entity. studentDataDataContext db = new Entity. studentDataDataContext (); // query the var student = db. students. where (n => n. sex = 0); // output the Student name Console. writeLine ("_____________ simple query __________________________________"); Console. writeLine ("Name of male output student gender:"); foreach (var stu in student) {Console. writeLine (stu. studentName);} Console. writeLine ("__________ querying partial field queries (querying StudentName and StudentNo) _____________"); var student2 = db. students. where (n => n. sex = 0 ). select (a => new {. studentName,. studentNo }). toList (); foreach (var stu in student2) {Console. writeLine (stu. studentName + "" + stu. studentNo);} Console. writeLine ("__________ querying a single record (querying records whose Student name is CoolHots) _____________"); var student3 = db. students. firstOrDefault (c => c. studentName = "CoolHots"); Console. writeLine (student3.StudentName + "" + student3.StudentNo + "" + student3.StudentID); Console. writeLine ("__________ left join query _____________"); var student4 = db. students. where (n => n. sex = 0 ). select (m => new {m. studentID, m. studentName, m. studentNo, ClassName = db. classes. firstOrDefault (a =>. classID = m. classID ). className }). toList (); foreach (var stu in student4) {Console. writeLine (stu. studentName + "" + stu. className);} Console. readLine ();}

 

 

[10 days to learn how to use Linq to sql1_the third day --data query .rar

 

This article was originally compiled by CoolHots. for reprinting, please keep the link: [10 days to learn Linq to SQL] The third day -- Data Query

Home page: CoolHots technology sharing