Linq to SQL: Second of integrated database language query

Linq to SQL: Second of integrated database language query

 
Step 5: Create Your Object Model
1. Open the Data Connections Tree View
2. Expand the Northwind folder
3. Expand the Tables folder
4. Double-click solution explorer to open the Northwind. dbml file.
5. Drag the MERs table from the table folder to the Linq to SQL designer.
6. Drag the Orders table from the table folder to the Linq to SQL designer.
7. Drag the Employees table from the table folder to the Linq to SQL designer.
Step 6: Query your object model
1. Press F5 to debug your program
As you can see, the designer writes all the "plumbing" code for you. Your main program runs normally!
Step 7: map a stored procedure
We have learned how to map tables to objects and how to declare associations between two tables. Now we want you to know how to map a stored procedure in a database to our object model.
1. Open the Data Connections Tree View
2. Expand the Northwind folder
3. Expand the Stored Procedures folder
4. Double-click solution explorer to open the Northwind. dbml file.
5. Drag Ten Most Expensive Products from the Stored Procedures folder to the designer
6. make the following changes to the Main method in your program and use the object model created from the designer.
Sub Main ()
'If we save the connection string in the designer, we do not need to provide the connection string.
Dim db As New NorthwindDataContext ()
Dim q = From customer In db. MERs _
Where customer. Region = Nothing _
Select customer. ContactName
For Each cust In q
Console. WriteLine (cust)
Next
Console. ReadLine ()
End Sub
7. Press F5 to debug this program
When you enter this code, note that it works like this. In IDE, intelliisense will display your Stored Procedure Ten_Most_Expensive_Products as a method generated by a designer in a strongly typed DataContext. It also prompts you that the type Ten_Most_Expensive_Product generated by the designer contains two strongly-typed attributes that map the fields returned from the stored procedure. Note: If you copy and paste the above Code: Simply enter "p." To observe the topic we are talking about.
Step 8: Search for a New result set
So far, we have run a query that can accept the complete object. However, you can only query the required attributes. You may also need to create a composite query. In Traditional SQL, a set containing any column can be returned as a result set. In Linq to SQL, you can perform such tasks by using the anonymous type.
1. modify the code in the Main method to create a query that only receives the ContactName attribute and displays it:
Sub Main ()
'If we save the connection string in the designer, we do not need to provide the connection string.
Dim db As New NorthwindDataContext ()
Dim q = From customer In db. MERs _
Where customer. Region = Nothing _
Select Company = customer. CompanyName ,_
Contact = customer. ContactName
For Each cust In q
Console. WriteLine ("{0}/{1}", cust. Contact, cust. Company)
Next
Console. ReadLine ()
End Sub
2. modify the code again to create a new object that contains the desired data and then output it:
Sub Main ()
'If we save the connection string in the designer, we do not need to provide the connection string.
Dim db As New NorthwindDataContext ()
Dim ids = From customer In db. MERs _
From emp In db. Employees _
Where customer. City = emp. City _
Select emp. EmployeeID _
Distinct
For Each id In ids
Console. WriteLine (id)
Next
Console. ReadLine ()
End Sub
Sub Main ()
'If we save the connection string in the designer, we do not need to provide the connection string.
Dim db As New NorthwindDataContext ()
Dim ids = From customer In db. MERs _
From emp In db. Employees _
Where customer. City = emp. City _
Select emp. EmployeeID _
Distinct
For Each id In ids
Console. WriteLine (id)
Next
Console. ReadLine ()
End Sub
3. Press F5 to debug this program
Note that this operator does not have a corresponding type name during runtime. The reason is that the compiler will create a new anonymous type based on the name and type of the selected column. You also need to note that the anonymous members have been renamed to Company and Contact. The specified name is optional. By default, it is named as the same as the source field name in the ing object. Finally, when the For Each syntax is executed, a new type of instance is referenced and all its types can be accessed.
4. modify these documents to complete a Join operation:
Sub Main ()
'If we save the connection string in the designer, we do not need to provide the connection string.
Dim db As New NorthwindDataContext ()
Dim ids = From customer In db. MERs _
From emp In db. Employees _
Where customer. City = emp. City _
Select emp. EmployeeID _
Distinct
For Each id In ids
Console. WriteLine (id)
Next
Console. ReadLine ()
End Sub
5. Press F5 to debug the entire solution.
The preceding example illustrates that an SQL-style Join can also be used without explicit Association and navigation. It also shows that only a single special attribute can be selected to send the entire object to the program.