There is a subtle difference between working with a table or a view in the EDM. Shown below is both the Orders table, and a view (SELECT * FROM Orders). Note that when working with the table, the foreign key fields such as EmployeeID are hidden in the model.

This means that when working with the view a direct reference can be made to a foreign key, but when working with a table forming part of a relationship to other tables the foreign key is referenced from the related table, for example Employees.EmployeeId.

Referencing a foreign key

Note that if the foreign key and matching key in the other table do not have the same name it is the name in the related table which needs to be referenced. In the example below ShipVia is the foreign key name, and Shippers.ShipperId will be the available reference.

The following example shows a code snippet which illustrates when binding to the foreign key how this is achieved when working with the Orders table.

It is also then not uncommon to add an Include statement as shown below when creating the data source for the main user interface controls to support filtering on the foreign keys. This would allow a Where clause to be added which makes reference to Shippers.ShipperID.

When working directly with a view, which will expose the foreign key, the above referencing mechanisms do not apply.

If you attempt to switch the source of data in the above example to use a view rather than the table, then in this case the following error message is encountered:-

'Customers' is not a member of type 'NorthwindModel.vw_Orders' in the currently loaded schemas., near multipart identifier, line 6, column 1.

Comment

The above could mean that a strategy which utilises tables will require more subsequent changes if at a later point in time it is switched from using table to using views. This depends a lot on whether you view the EDM as a means to an end in terms of providing a technology which effectively acts to enable you to effectively update the data in your database, or you wish to more fully embrace the object oriented vision of this technology. It also depends on whether you are starting to construct an application from scratch or convert an existing application.

This work has been conducted with Runtime Version:2.0.50727.4016, note that all Views in the Entity Data Model are Read-Only

Top of Page

MUST+WEB converting Access Databases to .net with Visual Studio or Visual Studio Express. Supports development in Visual Basic and C# using either SQL, LINQ or EntityData Model data sources. Click below to improve your development productivity.

Top of Page

by Andrew Couch Office Access MVP, Director ASC Associates andy@ascassociates.biz