How to map a View on a collection of Complex Type?

With EF, when we want to use views, we have to map them on Entity Type.

The problem is the fact that Entity Type must have a key. In EF, we have Complex Type which doesn’t have Entity Key but we can’t map views on Complex Type.

However, with EF4, we can map SSDL Function on Complex Types. SSDL Function is used for Stored Procedures but we also can define ourselves the query to execute in the SSDL.

By default, EF uses all non nullable columns as key when we haven’t got any key in our DB (which is true with Views).

So my idea is to use SSDL Function in order to map views on Complex Type.

First, we will import a View from Database. In my sample, Invoices from Northwind.

Then I open the edmx in xml and I delete all CSDL Invoice Entity Type keys.

Then I open the edmx with the designer. I select all Invoice properties, right click and Refactor into New Complex Type.

Then I delete Invoice Entity Type and refuse to also delete Invoices SSDL Type.

I rename my new Complex Type to Invoice.

Then I open the edmx in xml.

Copy the SQL query of the generated EntitySet Invoices:

<EntitySet Name="Invoices" EntityType="MyNorthwindEFModel.Store.Invoices" store:Type="Views" store:Schema="dbo" store:Name="Invoices">
  <DefiningQuery>
    SELECT 
    [Invoices].[ShipName] AS [ShipName], 
    [Invoices].[ShipAddress] AS [ShipAddress], 
    [Invoices].[ShipCity] AS [ShipCity], 
    [Invoices].[ShipRegion] AS [ShipRegion], 
    [Invoices].[ShipPostalCode] AS [ShipPostalCode], 
    [Invoices].[ShipCountry] AS [ShipCountry], 
    [Invoices].[CustomerID] AS [CustomerID], 
    [Invoices].[CustomerName] AS [CustomerName], 
    [Invoices].[Address] AS [Address], 
    [Invoices].[City] AS [City], 
    [Invoices].[Region] AS [Region], 
    [Invoices].[PostalCode] AS [PostalCode], 
    [Invoices].[Country] AS [Country], 
    [Invoices].[Salesperson] AS [Salesperson], 
    [Invoices].[OrderID] AS [OrderID], 
    [Invoices].[OrderDate] AS [OrderDate], 
    [Invoices].[RequiredDate] AS [RequiredDate], 
    [Invoices].[ShippedDate] AS [ShippedDate], 
    [Invoices].[ShipperName] AS [ShipperName], 
    [Invoices].[ProductID] AS [ProductID], 
    [Invoices].[ProductName] AS [ProductName], 
    [Invoices].[UnitPrice] AS [UnitPrice], 
    [Invoices].[Quantity] AS [Quantity], 
    [Invoices].[Discount] AS [Discount], 
    [Invoices].[ExtendedPrice] AS [ExtendedPrice], 
    [Invoices].[Freight] AS [Freight]
    FROM [dbo].[Invoices] AS [Invoices]
  </DefiningQuery>
</EntitySet>

Then delete this EntitySet and its Entity Type (SSDL) Invoices.

Then, add the following SSDL Function:

<Function Name="GetInvoices" IsComposable="false">
  <CommandText>
    SELECT
    [Invoices].[ShipName] AS [ShipName],
    [Invoices].[ShipAddress] AS [ShipAddress],
    [Invoices].[ShipCity] AS [ShipCity],
    [Invoices].[ShipRegion] AS [ShipRegion],
    [Invoices].[ShipPostalCode] AS [ShipPostalCode],
    [Invoices].[ShipCountry] AS [ShipCountry],
    [Invoices].[CustomerID] AS [CustomerID],
    [Invoices].[CustomerName] AS [CustomerName],
    [Invoices].[Address] AS [Address],
    [Invoices].[City] AS [City],
    [Invoices].[Region] AS [Region],
    [Invoices].[PostalCode] AS [PostalCode],
    [Invoices].[Country] AS [Country],
    [Invoices].[Salesperson] AS [Salesperson],
    [Invoices].[OrderID] AS [OrderID],
    [Invoices].[OrderDate] AS [OrderDate],
    [Invoices].[RequiredDate] AS [RequiredDate],
    [Invoices].[ShippedDate] AS [ShippedDate],
    [Invoices].[ShipperName] AS [ShipperName],
    [Invoices].[ProductID] AS [ProductID],
    [Invoices].[ProductName] AS [ProductName],
    [Invoices].[UnitPrice] AS [UnitPrice],
    [Invoices].[Quantity] AS [Quantity],
    [Invoices].[Discount] AS [Discount],
    [Invoices].[ExtendedPrice] AS [ExtendedPrice],
    [Invoices].[Freight] AS [Freight]
    FROM [dbo].[Invoices] AS [Invoices]
  </CommandText>
</Function>

Now, I open the edmx with the designer.

In the model browser window, in Northwind.Store / Stored Procedures, right click on your GetInvoies / Add Function Import.

Specify that this function returns a collection of Complex Type Invoice.

Here we are.

I can now use my Invoices view mapped on my Complex Type.

using (var context = new NorthwindEntities())
{
    List<Invoice> invoices = context.GetInvoices().ToList();
}

 

However, this solution isn’t perfect because we can’t use L2E on Invoices with this way.

Hope that helps.

This entry was posted in 7671, 7674, 7675. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>