Share via

Entity Framework FAQ: Mapping and Modeling

  • Article

   

[[articles:Entity Framework FAQ|Back to EF FAQs Table of Contents]] 

How can I map a stored procedure?

The EF mapping system supports mapping stored procedures for all four CRUD (Create, Read, Update, and Delete) operations.  For more details and a full example of reading entities from a stored procedure, see:

How can I map read-only entities?  How can I use Defining QueryViews in mapping files?

One way to map read-only entities is to use a Defining Query.  See Mapping Read-only Entities and QueryView Element (EntitySetMapping) for more details.

When doing model first, how can I customize the DDL script generated from my model?

When you generate the DDL script from the model, there are cases when you might want to customize the generated SQL. For example, when you have a property of binary type in the model and you want to map it to timestamp or rowversion (rowversion should be used instead of timestamp starting with SQL Server 2008) currently, you cannot do it in the designer. You can customize the T4 template that does the DDL generation script by doing the following:

1. Add a structured annotation to the CSDL, by declaring your own namespace and then adding an element using that namespace:

<Schema xmlns=" http://schemas.microsoft.com/ado/2008/09/edm"
    ...
         xmlns:my="http://tempuri.org/DatabaseTypes">
<Property Type="Binary" Name="TimeStamp" Nullable="false">
     <my:databasetype edmx:CopyToSSDL="true">TIMESTAMP</my:databasetype>
</Property>

For more information, see http://blogs.msdn.com/b/efdesign/archive/2008/08/12/structural-annotations-one-pager.aspx.

The CopyToSSDL annotation tells the database generation pipeline to copy the annotation to the SSDL.

  1. Make a copy of the SSDLToSQL10.tt file, located in `%localappdata%\Microsoft\AppId]\10.0\Extensions\Microsoft\Entity Framework Tools\DBGen` directory and place the copy in the same or a different directory. Note that it is not recommended to modify the original SSDLToSQL10.tt.
  2. Modify the copy of the .tt file so the model-first workflow will read this annotation and use it to determine the database type, rather than using the built-in logic to generate the DDL script.

For example, find the following line of code in the .tt file:

[<#=Id(prop.Name)#>] <#=prop.ToStoreType()#>   
              <#=WriteIdentity(prop, targetVersion)#>
              <#=WriteNullable(prop.Nullable)#>
              <#=(p < entitySet.ElementType.Properties.Count - 1) ? "," : ""#>

And replace it with the code that reads the annotation (if one exists) and uses its value instead of the built-in value for the database type:

     [<#=Id(prop.Name)#>]
           <#  if (prop.MetadataProperties.Contains("http://tempuri.org/DatabaseTypes:databasetype"))  
           {   
                  MetadataProperty annotationProperty =    prop.MetadataProperties[ "http://tempuri.org/DatabaseTypes:databasetype"];   
                  XElement e = XElement.Parse(annotationProperty.Value.ToString());   
                  string value = e.Value.Trim();  
           #> <#=value#> <#  
           } else {    
             #> <#=prop.ToStoreType()#> <# }
             #> <#=WriteIdentity(prop, targetVersion)
             #>   <#=WriteNullable(prop.Nullable)
             #>   <#=(p < entitySet.ElementType.Properties.Count - 1) ? "," : ""
             #>

Note: To make the preceding code work you need to add some assemblies and namespaces:

        <#@ assembly name="System.Xml.Linq" #>  
        <#@ assembly name="System.Xml" #>
        <#@ import namespace="System.Xml.Linq" #>
        <#@ import namespace="System.Xml" #>

       

  • Specify the new file name for your model, so the Create Database Wizard will now use the specified text template when generating DDL from SSDL. To do this, right-click the Entity Designer surface and set the new value of the DDL Generation Template property to the path of your custom .tt file.  For more information, see How to: Customize Database Generation.

Why do I get "Error 2062: no mapping between entity set and association set?"

You will get this error when you start with model first and you haven't generated the tables yet. The model will not validate until all objects in the model are mapped to the database. After you do Generated Database from Model, this error should go away.

I never delete records from my tables, but I do have a flag that indicates whether a record is active or not. I never want to show the records that are not active on the client. How do I permanently filter them out?

You need to add a condition for the Active column of your entity type. You then need to remove the mapping for the Active column and also remove the Active property from the entity type (in this order).  Consider the following example. Here we have an Entity2 type that has the Active property. This property is of a bit type in SQL Server.

We need to add the condition that specifies to only bring back entities where Active = 1, clear the mapping, and remove the Active property from Entity2, as show in the following screenshot.

Now when we execute a query to return results of the Entity2 type, only records where Active is set to 1 will return.

 

foreach (var e in context.Entity2)
    Console.WriteLine(e.ID);

On the client you will probably also want to add some logic that takes care of not really deleting objects, but instead updating the Active column. For example:

 

public override int SaveChanges(SaveOptions options)  
{
   foreach (ObjectStateEntry entry in ObjectStateManager.GetObjectStateEntries(
      EntityState.Deleted))
   {
      // Change the status to Unchanged, since we don't want for the Entity Framework
      // to issue a delete command.
      Entity2 e = entry.Entity as Entity2;
      entry.ChangeState(EntityState.Unchanged);
 
      // Update the Active columns in the database.
      ExecuteStoreCommand("UPDATE Entity2 SET Active = 0 WHERE Entity2.ID = {0}", e.ID);
   }
   return base.SaveChanges(options);
  }

Is it possible to map views to complex types?

Yes. The Entity Framework supports views to complex type mapping.  Views really aren't any different than tables except that the EF doesn't have all the same schema information about them in the sense that it doesn't know the key values, etc.  Note that you cannot have an entity key of a complex type. Keys must be made up of one or more properties that are primitive types.

Is there a way to specify default values for columns? Some DateTime columns in my database are set to getdate(), but the EF is unaware of these default values.

If you set the StoreGeneratedPattern to Identity (if the value is only computed on insert) or Computed (if it is computed on every update), then when you call SaveChanges the EF will get the value generated in the database and bring it back to update your object.  The EF will not automatically set the value when you create a new object because the computation actually happens in the database.

Also note that there is a StoreGeneratedPattern property on properties of your entities in the EDM designer, but it is only used for model first.  If you reverse engineer your model from the database, then you need to manually update the SSDL section of your EDMX file to have the StoreGeneratedPattern attribute on the appropriate entity property.  This is something that should be addressed in future releases of the EF designer (so that the property in the designer works for both Model First and Database First scenarios).

Is there any way to map a stored procedure to an association set in the designer?

No.

[[articles:Entity Framework FAQ|Back to EF FAQs Table of Contents]]