LINQ experience (18) -- Views and inheritance support for LINQ to SQL statements

View

Similar to using a view and using a data table, you only need to drag the view from "server resource manager/Database Resource Manager" to the O/R designer to automatically create entity classes based on these views. We can operate the view like a data table. Note: The O/R designer is a simple object-link er, because it only supports ing. In other words, an object class can only have a ing relationship with a database table or view. Complex ing is not supported (for example, ing an object class to multiple tables ). However, you can map an object class to a view that joins multiple related tables. The following uses the Invoices and Quarterly Orders views in the NORTHWND database as an example to write two examples.

Query: anonymous type

The following code is used to query the invoice of ShipCity in London.

var q =    from i in db.Invoices    where i.ShipCity == "London"    select new    {        i.OrderID,        i.ProductName,        i.Quantity,        i.CustomerName    };

The generated SQL statement is similar to that of the data table:

SELECT [t0].[OrderID], [t0].[ProductName], [t0].[Quantity],[t0].[CustomerName] FROM [dbo].[Invoices] AS [t0]WHERE [t0].[ShipCity] = @p0-- @p0: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [London]

Query: Id ing form

The following example shows the orders for each quarter.

var q =    from qo in db.Quarterly_Orders    select qo;

The SQL statement is:

SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[City],[t0].[Country] FROM [dbo].[Quarterly Orders] AS [t0]

Inheritance support

Supports single-Table ing. The entire inheritance hierarchy is stored in a single database table. This table contains the flat union of all possible data columns of the entire hierarchy. (Union is the result of combining two tables into one table. The combined table contains rows in any original table .) The column in each row does not apply to null for the instance type indicated by this row.
The single-Table ing policy is the simplest form of inheritance representation, which provides good performance features for many different types of queries. If we want to implement this ing in LINQ to SQL, attribute and Attribute must be specified in the root class of the hierarchy ). We can also use the O/R designer to map the inheritance hierarchy, which automatically generates code.

In order to demonstrate the following examples, we design the class shown in and Its Inheritance relationships in the O/R designer.

Let's take a look at the generated code when learning it!

You can set the ing hierarchy as follows:

1. Add the TableAttribute attribute to the root class.
2. Add the InheritanceMappingAttribute attribute to each class in the hierarchy and add it to the root class. Each InheritanceMappingAttribute defines a Code attribute and a Type attribute. The value of the Code attribute is displayed in the IsDiscriminator column of the database table to indicate the class or subclass to which the row data belongs. The Type property value specifies the class or subclass represented by the key value.
3. Only one of the InheritanceMappingAttribute attributes adds an IsDefault attribute to specify the rollback ing when the authenticator value in the database table does not match any Code value in the inheritance ing.
4. Add an IsDiscriminator attribute for the columnattriator attribute to indicate that this is the column that stores the Code value.

The following is the framework of the Code generated by this figure (because there are too many generated code, I deleted a lot of "branches" and only kept the main framework for pointing out its actual quality ):

[Table(Name = "dbo.Contacts")][InheritanceMapping(Code = "Unknown", Type = typeof(Contact),                    IsDefault = true)][InheritanceMapping(Code = "Employee", Type = typeof(EmployeeContact))][InheritanceMapping(Code = "Supplier", Type = typeof(SupplierContact))][InheritanceMapping(Code = "Customer", Type = typeof(CustomerContact))][InheritanceMapping(Code = "Shipper", Type = typeof(ShipperContact))]public partial class Contact :INotifyPropertyChanging, INotifyPropertyChanged{    [Column(Storage = "_ContactID",IsPrimaryKey = true,     IsDbGenerated = true)]    public int ContactID{ }    [Column(Storage = "_ContactType",IsDiscriminator = true)]    public string ContactType{ }}public abstract partial class FullContact : Contact{ }public partial class EmployeeContact : FullContact{ }public partial class SupplierContact : FullContact{ }public partial class CustomerContact : FullContact{ }public partial class ShipperContact : Contact{ }

1. General Form

We often write the form to query a single table.

var cons = from c in db.Contacts                                  select c;foreach (var con in cons) {    Console.WriteLine("Company name: {0}", con.CompanyName);    Console.WriteLine("Phone: {0}", con.Phone);    Console.WriteLine("This is a {0}", con.GetType());}

2. OfType format

Here, I only asked the customer to return their contact information.

var cons = from c in db.Contacts.OfType<CustomerContact>()           select c;

For preliminary study, we should look at the generated SQL statements, which is easy to understand. The contact information with ContactType as Customer is queried in the SQL statement.

SELECT [t0].[ContactType], [t0].[ContactName], [t0].[ContactTitle],[t0].[Address],[t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Fax],[t0].[ContactID], [t0].[CompanyName], [t0].[Phone] FROM [dbo].[Contacts] AS [t0]WHERE ([t0].[ContactType] = @p0) AND ([t0].[ContactType] IS NOT NULL)-- @p0: Input NVarChar (Size = 8; Prec = 0; Scale = 0) [Customer]

3. IS form

In this example, find the contact information of the sender.

var cons = from c in db.Contacts           where c is ShipperContact           select c;

The generated SQL statement is as follows: query the contact information of ContactType Shipper. In general, it seems like the above. In fact, there are a lot more columns queried here. It is actually all the fields in the Contacts table.

SELECT [t0].[ContactType], [t0].[ContactID], [t0].[CompanyName],[t0].[Phone],[t0].[HomePage], [t0].[ContactName],[t0].[ContactTitle], [t0].[Address], [t0].[City],[t0].[Region], [t0].[PostalCode], [t0].[Country],[t0].[Fax],[t0].[PhotoPath], [t0].[Photo], [t0].[Extension]FROM [dbo].[Contacts] AS [t0] WHERE ([t0].[ContactType] = @p0)AND ([t0].[ContactType] IS NOT NULL)-- @p0: Input NVarChar (Size = 7; Prec = 0; Scale = 0) [Shipper]

4. AS format

In this example, all of them are available.

var cons = from c in db.Contacts           select c as FullContact;

The SQL statement is as follows: query the entire Contacts table.

SELECT [t0].[ContactType], [t0].[HomePage], [t0].[ContactName],[t0].[ContactTitle],[t0].[Address], [t0].[City],[t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Fax], [t0].[ContactID], [t0].[CompanyName], [t0].[Phone], [t0].[PhotoPath],[t0].[Photo], [t0].[Extension] FROM [dbo].[Contacts] AS [t0]

5. Cast format

Use Case to find the contact information of customers in London.

var cons = from c in db.Contacts           where c.ContactType == "Customer" &&                      ((CustomerContact)c).City == "London"           select c;

The SQL statement is generated as follows. You can understand it yourself.

SELECT [t0].[ContactType], [t0].[ContactID], [t0].[CompanyName],[t0].[Phone], [t0].[HomePage],[t0].[ContactName],[t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region],[t0].[PostalCode], [t0].[Country], [t0].[Fax], [t0].[PhotoPath],[t0].[Photo], [t0].[Extension]FROM [dbo].[Contacts] AS [t0]WHERE ([t0].[ContactType] = @p0) AND ([t0].[City] = @p1)-- @p0: Input NVarChar (Size = 8; Prec = 0; Scale = 0) [Customer]-- @p1: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [London]

6. UseAsDefault format

When a record is inserted, the default ing relationship is used, but the inherited relationship is used during query. The generated SQL statement is straightforward.

// Insert a data record using the normal ing relationship by default. Contact contact = new Contact () {ContactType = null, CompanyName = "Unknown Company ", phone = "333-444-5555"}; db. contacts. insertOnSubmit (contact); db. submitChanges (); // by default, the inherited ing relationship var con = (from c in db. contacts where c. companyName = "Unknown Company" & c. phone = "333-444-5555" select c ). first ();

The SQL statement is as follows:

INSERT INTO [dbo].[Contacts]([ContactType], [CompanyName],[Phone]) VALUES (@p0, @p1, @p2)SELECT TOP (1) [t0].[ContactType], [t0].[ContactID],[t0].[CompanyName], [t0].[Phone],[t0].[HomePage],[t0].[ContactName], [t0].[ContactTitle], [t0].[Address],[t0].[City],[t0].[Region], [t0].[PostalCode], [t0].[Country],[t0].[Fax], [t0].[PhotoPath],[t0].[Photo], [t0].[Extension]FROM [dbo].[Contacts] AS [t0]WHERE ([t0].[CompanyName] = @p0) AND ([t0].[Phone] = @p1)-- @p0: Input NVarChar (Size = 15; Prec = 0; Scale = 0)    [Unknown Company]-- @p1: Input NVarChar (Size = 12; Prec = 0; Scale = 0)    [333-444-5555]

7. Insert a new record

This example shows how to insert a record of contact information of the shipper.

// 1. query before inserting. No data is available. var ShipperContacts = from SC in db. contacts. ofType <ShipperContact> () where SC. companyName = "Northwind Shipper" select SC; // 2. insert data ShipperContact nsc = new ShipperContact () {CompanyName = "Northwind Shipper", Phone = "(123)-456-7890"}; db. contacts. insertOnSubmit (nsc); db. submitChanges (); // 3. query data with a record ShipperContacts = from SC in db. contacts. ofType <ShipperContact> () where SC. companyName = "Northwind Shipper" select SC; // 4. delete record db. contacts. deleteOnSubmit (nsc); db. submitChanges ();

The SQL statement is as follows:

SELECT COUNT(*) AS [value] FROM [dbo].[Contacts] AS [t0]WHERE ([t0].[CompanyName] = @p0) AND ([t0].[ContactType] = @p1) AND ([t0].[ContactType] IS NOT NULL)-- @p0: Input NVarChar [Northwind Shipper]-- @p1: Input NVarChar [Shipper]INSERT INTO [dbo].[Contacts]([ContactType], [CompanyName], [Phone])VALUES (@p0, @p1, @p2)-- @p0: Input NVarChar  [Shipper]-- @p1: Input NVarChar  [Northwind Shipper]-- @p2: Input NVarChar  [(123)-456-7890]SELECT COUNT(*) AS [value] FROM [dbo].[Contacts] AS [t0]WHERE ([t0].[CompanyName] = @p0) AND ([t0].[ContactType] = @p1)AND ([t0].[ContactType] IS NOT NULL)-- @p0: Input NVarChar [Northwind Shipper]-- @p1: Input NVarChar [Shipper]DELETE FROM [dbo].[Contacts] WHERE ([ContactID] = @p0) AND([ContactType] = @p1) AND ([CompanyName] = @p2) AND ([Phone] = @p3)-- @p0: Input Int  [159]-- @p1: Input NVarChar  [Shipper]-- @p2: Input NVarChar  [Northwind Shipper]-- @p3: Input NVarChar  [(123)-456-7890]-- @p4: Input NVarChar  [Unknown]-- @p5: Input NVarChar (Size = 8; Prec = 0; Scale = 0) [Supplier]-- @p6: Input NVarChar (Size = 7; Prec = 0; Scale = 0) [Shipper]-- @p7: Input NVarChar (Size = 8; Prec = 0; Scale = 0) [Employee]-- @p8: Input NVarChar (Size = 8; Prec = 0; Scale = 0) [Customer]

This series of links: navigation to the LINQ Series

Recommended resources of LINQ

Special topics: http://kb.cnblogs.com/zt/linq/ on all aspects of the entry, advanced, in-depth articles on LINQ.
LINQ group: a good place to learn questions or questions about http://space.cnblogs.com/group/linq.

This article is based on the signature 2.5 mainland China license agreement. You are welcome to reprint, interpret, or use it for commercial purposes. However, you must keep this article's signature Li yongjing (including the link). For more information, see here. If you have any questions or authorization negotiation, please leave a message for me.