ADO. NET Entity Data Model entity key problem in multiple table join query

ADO. NET is the core of. NET interoperability with the database, and the Ado.net entity database enhances the ability of the. NET application to interconnect with the database, and we can easily strongly type data interoperation with the underlying database through the Ado.net Entity Data model. Greatly facilitates the design personnel, thus also enhances the database operation security. A very special problem has recently been encountered in the use of domain data services to siverlight [the results in the application are not the same as the results of the database] and after repeated trials, the root causes of the problem are finally found. That's ado.net. The Entity Data Model relies on entity keys, and its queries generate unique result sets for entity keys.

Now let's look at the specific process and how it is handled:

To manipulate the database tables, a vw_desksandusers view of two atomic tables was established in the database view, which was designed to connect existing user tables and desk data, so I did the following T-SQL view, as follows:

The results of the query in SQL Server 2008 are as follows:

That is to say, the expected results, [this step is successful]. The second step is next.

Step Two: Create a new siverlight business application siverlightdomaindb that will support Wcfria services in the generated project. This is necessary to support the Siverlight Domain Services.

Step three: Add a new item in the Web project that ado.net the Entity Data model, select the corresponding database connection and datasheet, view, and stored procedure in the wizard, and then generate the corresponding Entity data type and context, and get it. EDMX model Diagram. as follows:

According to VS2010, it generates two entity keys for us: deskid,isplaying (two fields in the desk table); Now rebuild the project.

Step Fourth: Add Web domain service items based on the entity model. (In this step you need to add the appropriate table and metadata information)

Step Fifth: In the Siverlight project, drag the DataGrid in the vw_desksandusers of the data source to Home.xaml, complete, Debug. Did not get the results above, but instead generated the following datagrid:

After careful observation, it turns out it was ado.net. The result set in the Entity Data Model relies on entity keys, meaning that the Siverlight domain service produces the result set of the query based on the unique entity key, resulting in the above data Deskid as the unique key.

Processing：

According to the above analysis, to get the expected results, you must set the appropriate entity key, in order not to affect the underlying SQL database, we need to modify. EDMX the entity key in the Model diagram (right-click to select or uncheck entity key):

Now rebuild the project and run the program, and the program gets the expected results.

The Siverlight business application as a template for data display provides a friendly interface for database rendering, and because of its optimized database handling, we have to consider some of their nuances when we use them [since there is no LINQ generated T in VS SQL query statement, so I'm just offering a simple solution, and I think the underlying reason is that the T-SQL statements generated in a LINQ query are different from the T-SQL statements in the database.