views:

304

answers:

0

Consider following example:

SQL Table Customer:

ID: Primary Key
Name: Name

SQL View ViewCustomerSelection:

ID: Link to Customer ID
SomeOption: Not important

I'd like to create ADO.NET an entity that only selects customers that have corresponding values in ViewCustomerSelection

Following does NOT work:

  • Create entity that maps to Customer and to ViewCustomerSelection without using any conditions and any fields from ViewCustomerSelection : ViewCustomerSelection is not included in SQL and all customers are returned.
  • Include a field from ViewCustomerSelection : Correct selection is returned but now Customer object is not updateable
  • Specify "not null" condition on SomeOption field : error - "Property ViewCustomerSelection.SomeOption with 'IsNull=false' condition must be mapped.
  • Specify "not null" condition on ViewCustomerSelection.ID field : error - "Entity type contains a condition on its primary key. Please remove the condition from the mapping.

Clarification

Database definition:

create table Customer {
ID [int] IDENTITY(1,1) NOT NULL,
Name [varchar](50) NOT NULL
} CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)

create table ViewCustomerSelection
as select customer.id, ToUpper(customer.name) as SomeOption where customer.name like 'A%'

I'd like to have entity Customer with ID and name, that only has records for records present in ViewCustomerSelection (note - in actuality the view is more complicated and can not be expressed using simple conditions)