How to query and add a SQL statement using LINQ

 

This document uses the customers and orders tables in the northwind database as examples to illustrate how to query and add a table in the LINQ to SQL statement.

First, create datacontext and the corresponding entity class through the LINQ to SQL file wizard. If you do not know how to do this, see: defining data model classes.

Create a customer object and add the data to the database.

  NorthwindDataContext ctx = new NorthwindDataContext();  Customer c = new Customer { CustomerID = "Test1",      CompanyName = "Test Company1" };  ctx.Customers.Add(c);  ctx.SubmitChanges();

Run this code and you can see it in SQL profile.

  exec sp_executesql N'INSERT INTO [dbo].[Customers]([CustomerID], [CompanyName], [ContactName]...

This SQL statement inserts data into the customer table. Run the following code to check whether the added data can be queried from the database.

  Customer c1 = ctx.Customers.Single(c => c.CustomerID == "Test1");  Console.WriteLine(c1.CustomerID + "  " + c1.CompanyName);

After running this code, you can see that this SQL statement is executed in SQL profile.

  exec sp_executesql N'SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName]...

The correct results are printed on the screen.

 

Now let the following code be executed once to see what will happen?

  NorthwindDataContext ctx = new NorthwindDataContext();  Customer c = new Customer  {      CustomerID = "Test2",      CompanyName = "Test Company2"  };  ctx.Customers.Add(c);  ctx.SubmitChanges();  Customer c1 = ctx.Customers.Single(ct => ct.CustomerID == "Test2");  Console.WriteLine(c1.CustomerID + "  " + c1.CompanyName);

Through the records in SQL profile, we can find that only the insert statement is executed this time, but the query statement is not executed, but the query returns the correct result. In video, mtauly indicates that this is a cache mechanism of LINQ to SQL, called Identity cache. When performing a query, if datacontext finds that the query condition is a primary key and only a primary key, it first searches for it in the cache. if it finds it, it returns the object. It can be guessed that it is inside datacontext, it may be easy to use a hashtable, and use the primary key value as the key to cache the operated objects. When we use another datacontext for queries or the query condition contains not only the primary key, it will perform a query on the database.

 

Next, let's take a look at how to query one-to-multiple relationships in the LINQ to SQL statements. It is actually very simple:

  Customer c = ctx.Customers.Single(ct => ct.CompanyName == "Test Company1");  foreach (var order in c.Orders)  {      Console.WriteLine(order);  }

By observing the SQL profile, we found that only C. orders will query the orders table, but will not return all related orders when querying the customer, which is similar to the lazy initialization mechanism in hibernate. In addition, the LINQ to SQL provides a mechanism called delay loaded, Which is used on fields. This means that only when these fields are used can a database query be conducted to select these fields for use. For tables that contain big data fields (picture and BLOB, this mechanism can also improve a lot of efficiency.