August 2008 - Posts

EF and WPF

I had a question today which allow me to do my first WPF poject. Smile

The guy told me that he has a master detail scenario with two windows: one to edit masters and one to edit details. In details, he wants to choose the master with a combo.

He wants that when he adds one master, the combo adds it also without having to rebind it and without having to save the context.

How to do this?

I developp a class for this:

public class ContextEntitiesLoaded<T> : IEnumerable<T>, INotifyCollectionChanged

{

    private ObjectContext _context;

    private EntityState _states = EntityState.Added | EntityState.Modified | EntityState.Unchanged;

 

    public ContextEntitiesLoaded(ObjectContext context)

    {

        _context = context;

        _context.ObjectStateManager.ObjectStateManagerChanged += (sender, e) =>

        {

            if (e.Element is T)

                switch (e.Action)

                {

                    case CollectionChangeAction.Add:

                    case CollectionChangeAction.Remove:

                        if (e.Element is T)

                            OnNotifyCollectionChanged();

                        break;

                }

        };

    }

 

    public EntityState States

    {

        get { return _states; }

        set { _states = value; }

    }

    public Func<T, object> SortExpression { get; set; }

 

    protected virtual void OnNotifyCollectionChanged()

    {

        if (CollectionChanged != null)

            CollectionChanged(this, new NotifyCollectionChangedEventArgs(NotifyCollectionChangedAction.Reset));

    }

 

    #region IEnumerable<T> Members

    public IEnumerator<T> GetEnumerator()

    {

        var q = _context.ObjectStateManager.GetObjectStateEntries(States).Select(se => se.Entity).OfType<T>();

        if (SortExpression != null)

            q = q.OrderBy(SortExpression);

        return q.GetEnumerator();

    }

    #endregion

    #region IEnumerable Members

    System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()

    {

        return GetEnumerator();

    }

    #endregion

 

    #region INotifyCollectionChanged Members

    public event NotifyCollectionChangedEventHandler CollectionChanged;

    #endregion

}

Then I need to get all the classes in the context and to bind them on the combo:

public partial class Window1 : Window

    public Window1()

    {

        InitializeComponent();

        ObjectDataProvider odp = this.FindResource("maSource") as ObjectDataProvider;

        if (odp != null)

        {

            foreach (var c in NorthwindEntities.Instance.Categories); // to load the categories on the context

            odp.ObjectInstance = new ContextEntitiesLoaded<Category>(NorthwindEntities.Instance) { SortExpression = c => c.CategoryName };

        }

    }

 

    [...]

}

TPH Is Not NULL mapped on a relationship

As I wrote in a previous post, with EF V1, there is a limitation on the mapping of columns with the condition Is Not Null: the property mapped to the column must be scalar and can't be (indirectly) a navigation property.

So how to do the scenario of my previous post?

With a SSDL View of course:

<EntitySet Name="CustomerContacts" EntityType="TestRelationshipsOnTPHModel.Store.CustomerContacts">

    <DefiningQuery>

        SELECT ContactId, CAST(CASE WHEN CustomerId IS NULL THEN 0 ELSE 1 END AS BIT) AS IsContactCustomer

        FROM Contacts

    </DefiningQuery>

</EntitySet>

<EntityType Name="CustomerContacts">

    <Key>

        <PropertyRef Name="ContactId" />

    </Key>

    <Property Name="ContactId" Type="int" Nullable="false" />

    <Property Name="IsContactCustomer" Type="bit" />

</EntityType>

Then you map it on your derived entity type (CustomerContact). So your MSL should be this:

<edmx:Mappings>

    <Mapping Space="C-S" xmlns="urn:schemas-microsoft-com:windows:storage:mapping:CS">

        <EntityContainerMapping StorageEntityContainer="TestRelationshipsOnTPHModelStoreContainer" CdmEntityContainer="TestRelationshipsOnTPHEntities">

            <EntitySetMapping Name="Contacts">

                <EntityTypeMapping TypeName="IsTypeOf(TestRelationshipsOnTPHModel.Contact)">

                    <MappingFragment StoreEntitySet="Contacts">

                        <ScalarProperty Name="ContactName" ColumnName="ContactName" />

                        <ScalarProperty Name="ContactId" ColumnName="ContactId" />

                    </MappingFragment>

                </EntityTypeMapping>

                <EntityTypeMapping TypeName="IsTypeOf(TestRelationshipsOnTPHModel.CustomerContact)">

                    <MappingFragment StoreEntitySet="CustomerContacts" >

                        <ScalarProperty Name="ContactId" ColumnName="ContactId" />

                        <Condition ColumnName="IsContactCustomer" Value="true" />

                    </MappingFragment>

                </EntityTypeMapping>

            </EntitySetMapping>

            <EntitySetMapping Name="Customers">

                <EntityTypeMapping TypeName="IsTypeOf(TestRelationshipsOnTPHModel.Customer)">

                    <MappingFragment StoreEntitySet="Customers">

                        <ScalarProperty Name="CompanyName" ColumnName="CompanyName" />

                        <ScalarProperty Name="CustomerId" ColumnName="CustomerId" />

                    </MappingFragment>

                </EntityTypeMapping>

            </EntitySetMapping>

            <AssociationSetMapping Name="CustomerCustomerContact" TypeName="TestRelationshipsOnTPHModel.CustomerCustomerContact" StoreEntitySet="Contacts">

                <EndProperty Name="CustomerContact">

                    <ScalarProperty Name="ContactId" ColumnName="ContactId" />

                </EndProperty>

                <EndProperty Name="Customer">

                    <ScalarProperty Name="CustomerId" ColumnName="CustomerId" />

                </EndProperty>

                <Condition ColumnName="CustomerId" IsNull="false" />

            </AssociationSetMapping>

        </EntityContainerMapping>

    </Mapping>

</edmx:Mappings>

Thanks to Srikanth for his help.

LINQ To Entities: stranger and stranger

I was looking for this LINQ To Entities "bug ?".

I watched the SQL generated queries and in fact it's even stranger than what I thought at first.

For the first LINQ To Entities query:

from c in context.Customers.Include("Orders")

where c.CompanyName.StartsWith("An")

let od = (from o in c.Orders

          select o.OrderDate).OrderByDescending(o => o).FirstOrDefault()

orderby od descending

select c;

I get (with foreach (var c in cQuery) Console.WriteLine(c.CompanyName);):

Ana Trujillo Emparedados y helados
Antonio Moreno Taquería

The SQL generated query is:

SELECT
[Project2].[Address] AS [Address],
[Project2].[City] AS [City],
[Project2].[CompanyName] AS [CompanyName],
[Project2].[ContactName] AS [ContactName],
[Project2].[ContactTitle] AS [ContactTitle],
[Project2].[Country] AS [Country],
[Project2].[CustomerID] AS [CustomerID],
[Project2].[Fax] AS [Fax],
[Project2].[Phone] AS [Phone],
[Project2].[PostalCode] AS [PostalCode],
[Project2].[Region] AS [Region]
FROM ( SELECT
 [Extent1].[Address] AS [Address],
 [Extent1].[City] AS [City],
 [Extent1].[CompanyName] AS [CompanyName],
 [Extent1].[ContactName] AS [ContactName],
 [Extent1].[ContactTitle] AS [ContactTitle],
 [Extent1].[Country] AS [Country],
 [Extent1].[CustomerID] AS [CustomerID],
 [Extent1].[Fax] AS [Fax],
 [Extent1].[Phone] AS [Phone],
 [Extent1].[PostalCode] AS [PostalCode],
 [Extent1].[Region] AS [Region],
 (SELECT TOP (1) [Project1].[OrderDate] AS [OrderDate]
  FROM ( SELECT
   [Extent2].[OrderDate] AS [OrderDate]
   FROM [dbo].[Orders] AS [Extent2]
   WHERE [Extent1].[CustomerID] = [Extent2].[CustomerID]
  )  AS [Project1]
  ORDER BY [Project1].[OrderDate] DESC) AS [C1]
 FROM [dbo].[Customers] AS [Extent1]
 WHERE (CAST(CHARINDEX(N'An', [Extent1].[CompanyName]) AS int)) = 1
)  AS [Project2]
ORDER BY [Project2].[C1] DESC

What is very strange is the fact we don't get the orders. So in fact the Include doesn't do its job!

Now for the second one:

from c in context.Customers.Include("Orders")

where c.CompanyName.StartsWith("An")

orderby (from o in c.Orders

        select o.OrderDate).OrderByDescending(o => o).FirstOrDefault() descending

select c;

I have this result:

Ana Trujillo Emparedados y helados
Antonio Moreno Taquería
Ana Trujillo Emparedados y helados
Antonio Moreno Taquería
Ana Trujillo Emparedados y helados
Antonio Moreno Taquería
Ana Trujillo Emparedados y helados

with this SQL query:

SELECT
[Project2].[Address] AS [Address],
[Project2].[City] AS [City],
[Project2].[CompanyName] AS [CompanyName],
[Project2].[ContactName] AS [ContactName],
[Project2].[ContactTitle] AS [ContactTitle],
[Project2].[Country] AS [Country],
[Project2].[CustomerID] AS [CustomerID],
[Project2].[Fax] AS [Fax],
[Project2].[Phone] AS [Phone],
[Project2].[PostalCode] AS [PostalCode],
[Project2].[Region] AS [Region],
[Project2].[OrderID] AS [OrderID],
[Project2].[C1] AS [C1],
[Project2].[C3] AS [C2],
[Project2].[C2] AS [C3],
[Project2].[EmployeeID] AS [EmployeeID],
[Project2].[Freight] AS [Freight],
[Project2].[OrderDate1] AS [OrderDate],
[Project2].[OrderID1] AS [OrderID1],
[Project2].[RequiredDate] AS [RequiredDate],
[Project2].[ShipAddress] AS [ShipAddress],
[Project2].[ShipCity] AS [ShipCity],
[Project2].[ShipCountry] AS [ShipCountry],
[Project2].[ShipName] AS [ShipName],
[Project2].[ShippedDate] AS [ShippedDate],
[Project2].[ShipPostalCode] AS [ShipPostalCode],
[Project2].[ShipRegion] AS [ShipRegion],
[Project2].[ShipVia] AS [ShipVia],
[Project2].[CustomerID1] AS [CustomerID1]
FROM ( SELECT
 [Filter1].[Address] AS [Address],
 [Filter1].[City] AS [City],
 [Filter1].[CompanyName] AS [CompanyName],
 [Filter1].[ContactName] AS [ContactName],
 [Filter1].[ContactTitle] AS [ContactTitle],
 [Filter1].[Country] AS [Country],
 [Filter1].[CustomerID] AS [CustomerID],
 [Filter1].[Fax] AS [Fax],
 [Filter1].[Phone] AS [Phone],
 [Filter1].[PostalCode] AS [PostalCode],
 [Filter1].[Region] AS [Region],
 [Limit1].[OrderDate] AS [OrderDate],
 [Limit1].[OrderID] AS [OrderID],
 1 AS [C1],
 [Extent3].[CustomerID] AS [CustomerID1],
 [Extent3].[EmployeeID] AS [EmployeeID],
 [Extent3].[Freight] AS [Freight],
 [Extent3].[OrderDate] AS [OrderDate1],
 [Extent3].[OrderID] AS [OrderID1],
 [Extent3].[RequiredDate] AS [RequiredDate],
 [Extent3].[ShipAddress] AS [ShipAddress],
 [Extent3].[ShipCity] AS [ShipCity],
 [Extent3].[ShipCountry] AS [ShipCountry],
 [Extent3].[ShipName] AS [ShipName],
 [Extent3].[ShippedDate] AS [ShippedDate],
 [Extent3].[ShipPostalCode] AS [ShipPostalCode],
 [Extent3].[ShipRegion] AS [ShipRegion],
 [Extent3].[ShipVia] AS [ShipVia],
 CASE WHEN ([Extent3].[OrderID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2],
 CASE WHEN ([Extent3].[OrderID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C3]
 FROM    (SELECT [Extent1].[Address] AS [Address], [Extent1].[City] AS [City], [Extent1].[CompanyName] AS [CompanyName], [Extent1].[ContactName] AS [ContactName], [Extent1].[ContactTitle] AS [ContactTitle], [Extent1].[Country] AS [Country], [Extent1].[CustomerID] AS [CustomerID], [Extent1].[Fax] AS [Fax], [Extent1].[Phone] AS [Phone], [Extent1].[PostalCode] AS [PostalCode], [Extent1].[Region] AS [Region]
  FROM [dbo].[Customers] AS [Extent1]
  WHERE (CAST(CHARINDEX(N'An', [Extent1].[CompanyName]) AS int)) = 1 ) AS [Filter1]
 OUTER APPLY  (SELECT TOP (1) [Project1].[OrderDate] AS [OrderDate], [Project1].[OrderID] AS [OrderID]
  FROM ( SELECT
   [Extent2].[OrderDate] AS [OrderDate],
   [Extent2].[OrderID] AS [OrderID]
   FROM [dbo].[Orders] AS [Extent2]
   WHERE [Filter1].[CustomerID] = [Extent2].[CustomerID]
  )  AS [Project1]
  ORDER BY [Project1].[OrderDate] DESC ) AS [Limit1]
 LEFT OUTER JOIN [dbo].[Orders] AS [Extent3] ON [Filter1].[CustomerID] = [Extent3].[CustomerID]
)  AS [Project2]
ORDER BY [Project2].[OrderDate] DESC, [Project2].[CustomerID] ASC, [Project2].[OrderID] ASC, [Project2].[C3] ASC

which returns this result:

CompanyName = Ana Trujillo Emparedados y helados, OrderID1 = 10926
CompanyName = Antonio Moreno Taquería, OrderID1 = 10856
CompanyName = Ana Trujillo Emparedados y helados, OrderID1 = 10759
CompanyName = Antonio Moreno Taquería, OrderID = 10682
CompanyName = Antonio Moreno Taquería, OrderID = 10677
CompanyName = Ana Trujillo Emparedados y helados, OrderID = 10625
CompanyName = Antonio Moreno Taquería, OrderID = 10573
CompanyName = Antonio Moreno Taquería, OrderID = 10535
CompanyName = Antonio Moreno Taquería, OrderID = 10507
CompanyName = Antonio Moreno Taquería, OrderID = 10365
CompanyName = Ana Trujillo Emparedados y helados , OrderID = 10308

The include is correct here BUT not my LINQ To Entities result.

As it seems that the EF Materializer doesn't consider identical adjacent rows to determine customers, I think EF should generate this SQL Query:

SELECT
[Project2].[Address] AS [Address],
[Project2].[City] AS [City],
[Project2].[CompanyName] AS [CompanyName],
[Project2].[ContactName] AS [ContactName],
[Project2].[ContactTitle] AS [ContactTitle],
[Project2].[Country] AS [Country],
[Project2].[CustomerID] AS [CustomerID],
[Project2].[Fax] AS [Fax],
[Project2].[Phone] AS [Phone],
[Project2].[PostalCode] AS [PostalCode],
[Project2].[Region] AS [Region],
[Project2].[OrderID] AS [OrderID],
[Project2].[C1] AS [C1],
[Project2].[C3] AS [C2],
[Project2].[C2] AS [C3],
[Project2].[EmployeeID] AS [EmployeeID],
[Project2].[Freight] AS [Freight],
[Project2].[OrderDate1] AS [OrderDate],
[Project2].[OrderID1] AS [OrderID1],
[Project2].[RequiredDate] AS [RequiredDate],
[Project2].[ShipAddress] AS [ShipAddress],
[Project2].[ShipCity] AS [ShipCity],
[Project2].[ShipCountry] AS [ShipCountry],
[Project2].[ShipName] AS [ShipName],
[Project2].[ShippedDate] AS [ShippedDate],
[Project2].[ShipPostalCode] AS [ShipPostalCode],
[Project2].[ShipRegion] AS [ShipRegion],
[Project2].[ShipVia] AS [ShipVia],
[Project2].[CustomerID1] AS [CustomerID1]
FROM ( SELECT
 [Filter1].[Address] AS [Address],
 [Filter1].[City] AS [City],
 [Filter1].[CompanyName] AS [CompanyName],
 [Filter1].[ContactName] AS [ContactName],
 [Filter1].[ContactTitle] AS [ContactTitle],
 [Filter1].[Country] AS [Country],
 [Filter1].[CustomerID] AS [CustomerID],
 [Filter1].[Fax] AS [Fax],
 [Filter1].[Phone] AS [Phone],
 [Filter1].[PostalCode] AS [PostalCode],
 [Filter1].[Region] AS [Region],
 [Limit1].[OrderDate] AS [OrderDate],
 [Limit1].[OrderID] AS [OrderID],
 1 AS [C1],
 [Extent3].[CustomerID] AS [CustomerID1],
 [Extent3].[EmployeeID] AS [EmployeeID],
 [Extent3].[Freight] AS [Freight],
 [Extent3].[OrderDate] AS [OrderDate1],
 [Extent3].[OrderID] AS [OrderID1],
 [Extent3].[RequiredDate] AS [RequiredDate],
 [Extent3].[ShipAddress] AS [ShipAddress],
 [Extent3].[ShipCity] AS [ShipCity],
 [Extent3].[ShipCountry] AS [ShipCountry],
 [Extent3].[ShipName] AS [ShipName],
 [Extent3].[ShippedDate] AS [ShippedDate],
 [Extent3].[ShipPostalCode] AS [ShipPostalCode],
 [Extent3].[ShipRegion] AS [ShipRegion],
 [Extent3].[ShipVia] AS [ShipVia],
 CASE WHEN ([Extent3].[OrderID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2],
 CASE WHEN ([Extent3].[OrderID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C3]
 FROM    (SELECT [Extent1].[Address] AS [Address], [Extent1].[City] AS [City], [Extent1].[CompanyName] AS [CompanyName], [Extent1].[ContactName] AS [ContactName], [Extent1].[ContactTitle] AS [ContactTitle], [Extent1].[Country] AS [Country], [Extent1].[CustomerID] AS [CustomerID], [Extent1].[Fax] AS [Fax], [Extent1].[Phone] AS [Phone], [Extent1].[PostalCode] AS [PostalCode], [Extent1].[Region] AS [Region]
  FROM [dbo].[Customers] AS [Extent1]
  WHERE (CAST(CHARINDEX(N'An', [Extent1].[CompanyName]) AS int)) = 1 ) AS [Filter1]
 OUTER APPLY  (SELECT TOP (1) [Project1].[OrderDate] AS [OrderDate], [Project1].[OrderID] AS [OrderID]
  FROM ( SELECT
   [Extent2].[OrderDate] AS [OrderDate],
   [Extent2].[OrderID] AS [OrderID]
   FROM [dbo].[Orders] AS [Extent2]
   WHERE [Filter1].[CustomerID] = [Extent2].[CustomerID]
  )  AS [Project1]
  ORDER BY [Project1].[OrderDate] DESC ) AS [Limit1]
 LEFT OUTER JOIN [dbo].[Orders] AS [Extent3] ON [Filter1].[CustomerID] = [Extent3].[CustomerID]
)  AS [Project2]
ORDER BY [Project2].[OrderDate] DESC, [Project2].[CustomerID] ASC, [Project2].[OrderID] ASC, [Project2].[C3] ASC

Indeed the ORDER By OrderDate is done before.

Posted by Matthieu MEZIL | with no comments

TPH limitation which should disappear with v2

When you have a condition like myColumn Is Not Null, you must map the column on a non nullable property BUT with EF v1, you can't map the column on a relationship (even if the cardinality is one).

For example,

I have two tables:

  • Customers
    • CustomerId (PK)
    • CompanyName
  • Contacts
    • ContactId (PK)
    • Name
    • CustomerId (FK to Customers.CustomerId NULL)

In my EDM, I want three entity types:

  • Customer
  • Contact (with no association to Customer)
  • CustomerContact (with an association to Customer)

For this,

  • I delete the relationship in MSL, CSDL and SSDL.
  • I use TPH between CustomerContact and Contact with CustomerId Is Null for Contact and CustomerId IsNot Null for CustomerContact.
  • Then I add an association between CustomerContact and Customer with a cardinality one to many.

I have an error:

Error 3022: Problem in Mapping Fragment starting at line 63: Property Contacts.CustomerId with 'IsNull=false' condition must be mapped.

But it’s wrong. I mapped Contacts.CustomerId on the relationship.

LINQ To Entities: Strange, very strange

From Northwind, if I run this query:

from c in context.Customers.Include("Orders")

where c.CompanyName.StartsWith("An")

let od = (from o in c.Orders

          select o.OrderDate).OrderByDescending(o => o).FirstOrDefault()

orderby od descending

select c;

I have the customers of these company:

Ana Trujillo Emparedados y helados
Antonio Moreno Taquería

Now if I do this:

from c in context.Customers.Include("Orders")

where c.CompanyName.StartsWith("An")

orderby (from o in c.Orders

          select o.OrderDate).OrderByDescending(o => o).FirstOrDefault() descending

select c;

I have this:

Ana Trujillo Emparedados y helados
Antonio Moreno Taquería
Ana Trujillo Emparedados y helados
Antonio Moreno Taquería
Ana Trujillo Emparedados y helados
Antonio Moreno Taquería
Ana Trujillo Emparedados y helados

Now, if I do this:

from c in context.Customers//.Include("Orders")

where c.CompanyName.StartsWith("An")

orderby (from o in c.Orders

          select o.OrderDate).OrderByDescending(o => o).FirstOrDefault() descending

select c;

I have this:

Ana Trujillo Emparedados y helados
Antonio Moreno Taquería

To finish, if I do this:

from c in context.Customers.Include("Orders")

where c.CompanyName.StartsWith("An")

orderby (from o in c.Orders

        select o.OrderDate).OrderByDescending(o => o).FirstOrDefault() descending

select c.CompanyName;

I have this:

Ana Trujillo Emparedados y helados
Antonio Moreno Taquería 

I also try to do this:

from c in context.Customers

where c.CompanyName.StartsWith("An")

let orders = c.Orders.OrderByDescending(o => o.OrderDate)

let lastOrderDate = orders.Select(o => o.OrderDate).FirstOrDefault()

orderby lastOrderDate descending

select new { Customer = c, Orders = orders };

I have this result:

Ana Trujillo Emparedados y helados
Antonio Moreno Taquería
Ana Trujillo Emparedados y helados
Antonio Moreno Taquería
Ana Trujillo Emparedados y helados
Antonio Moreno Taquería
Ana Trujillo Emparedados y helados

But without the Orders in the anonymous type:

from c in context.Customers

where c.CompanyName.StartsWith("An")

let orders = c.Orders.OrderByDescending(o => o.OrderDate)

let lastOrderDate = orders.Select(o => o.OrderDate).FirstOrDefault()

orderby lastOrderDate descending

select new { Customer = c };

I have this:

Ana Trujillo Emparedados y helados
Antonio Moreno Taquería 

TPH is more than a selection!

For some persons, the TPH is just a selection but in fact it's actually more than this.

Imagine the following table:

  • Id (PK)
  • Type int NULL

Imagine than we want to do TPH on Type column.

I had some questions like is it possible to have an OR. For example Type = 0 OR Type = 1.

The answer is no. Why?

When you use some entities, you probably want to do more than just read it. The SQL generated query for an INSERT include the condition value. So of course you can't have two values.

However, you can have an entity type for all the non managed cases. When you add a new entity of this type, the Type column will be NULL.

Now what happens if Type is NOT nullable?

In this case, you must define the condition value (or take your entity type abstract). You can define Type = 0 on the base entity type and Type = 1 on the derived one. If you insert a new derived entity type, you will have 1 in your DB. (it's like virtual override).

EF does a lot of checks to make sure of the EDM coherency with the DB and of the consistency of your EDM. For example, if you have a derived entity with condition Is Null and another with the condition Is Not Null, the base entity type must be abstract.

How to have a property on ObjectContext which returns the derived entities? v2

With Danny advices, I improve my custom code generator to do this in only one generation.

public class SampleEdmxCodeGenerator : BaseCodeGeneratorWithSite

{

    private EntityContainer _objectContext;

    private Dictionary<string, string> _entitySetNames;

    private Dictionary<string, List<string>> _typesHierarchyToAddInObjectContext;

 

    public Dictionary<string, string> EntitySetNames

    {

        get

        {

            if (_entitySetNames == null)

                _entitySetNames = new Dictionary<string, string>();

            return _entitySetNames;

        }

    }

    private Dictionary<string, List<string>> TypesHierarchyToAddInObjectContext

    {

        get

        {

            if (_typesHierarchyToAddInObjectContext == null)

                _typesHierarchyToAddInObjectContext = new Dictionary<string, List<string>>();

            return _typesHierarchyToAddInObjectContext;

        }

    }

    private IEnumerable<string> GetSubEntitiesOf(EntityType baseType)

    {

        var empty = Enumerable.Empty<string>();

        if (_typesHierarchyToAddInObjectContext == null)

            return empty;

        string baseTypeName = baseType.Name;

        if (!_typesHierarchyToAddInObjectContext.ContainsKey(baseTypeName))

            return empty;

        return TypesHierarchyToAddInObjectContext[baseTypeName];

    }

    private void AddSubEntities(string baseType, string typeToAdd)

    {

        string baseTypeName = baseType;

        if (!TypesHierarchyToAddInObjectContext.ContainsKey(baseTypeName))

            TypesHierarchyToAddInObjectContext.Add(baseTypeName, new List<string>());

        TypesHierarchyToAddInObjectContext[baseTypeName].Add(typeToAdd);

    }

 

    protected override string GetDefaultExtension()

    {

        return (".Designer" + base.GetDefaultExtension());

    }

 

    protected override byte[] GenerateCode(string inputFileContent)

    {

        byte[] generatedCodeAsBytes = null;

 

        try

        {

            XElement csdlContent = ExtractCsdlContent(inputFileContent);

            if (csdlContent == null)

            {

                throw new InvalidOperationException("No CSDL content in input file");

            }

 

            _objectContext = null;

            _entitySetNames = null;

            _typesHierarchyToAddInObjectContext = null;

 

            LanguageOption languageOption = LanguageOption.GenerateCSharpCode;

            string fileExtension = base.GetCodeProvider().FileExtension;

            if (fileExtension != null && fileExtension.Length > 0)

            {

                fileExtension = "." + fileExtension.TrimStart(".".ToCharArray());

            }

            if (fileExtension.EndsWith(".vb", StringComparison.InvariantCultureIgnoreCase))

            {

                languageOption = LanguageOption.GenerateVBCode;

            }

            else if (fileExtension.EndsWith(".cs", StringComparison.InvariantCultureIgnoreCase))

            {

                languageOption = LanguageOption.GenerateCSharpCode;

            }

            else

            {

                throw new InvalidOperationException("Unsupported project language. Only C# and VB are supported.");

            }

 

            if (base.CodeGeneratorProgress != null)

            {

                base.CodeGeneratorProgress.Progress(33, 100);

            }

 

            EntityClassGenerator classGenerator;

            IList<EdmSchemaError> errors = null;

            using (StringWriter codeWriter = new StringWriter(CultureInfo.InvariantCulture))

            {

                using (XmlReader csdlReader = csdlContent.CreateReader())

                {

                    classGenerator = new EntityClassGenerator(languageOption);

                    classGenerator.OnTypeGenerated += new TypeGeneratedEventHandler(OnTypeGenerated);

                    classGenerator.OnPropertyGenerated += new PropertyGeneratedEventHandler(OnPropertyGenerated);

 

                    foreach (var entityType in from et in csdlContent.Descendants("{http://schemas.microsoft.com/ado/2006/04/edm}EntityType")

                                               let etBaseType = et.Attributes("BaseType").FirstOrDefault()

                                               where etBaseType != null

                                               select new { EntityType = et.Attribute("Name").Value, BaseType = etBaseType.Value })

                    {

                        var baseType = entityType.BaseType;

                        string baseTypeTmp;

                        while ((baseTypeTmp = (from et in csdlContent.Descendants("{http://schemas.microsoft.com/ado/2006/04/edm}EntityType")

                                               where et.Attribute("Name").Value == baseType

                                               let etBaseType = et.Attributes("BaseType").FirstOrDefault()

                                               where etBaseType != null

                                               select etBaseType.Value).FirstOrDefault()) != null)

                            baseTypeTmp = baseType;

                        Func<string, string> getSimpleName = entityTypeName => entityTypeName.Substring(entityTypeName.IndexOf(".") + 1);

                        AddSubEntities(getSimpleName(baseType), getSimpleName(entityType.EntityType));

                    }

 

                    errors = classGenerator.GenerateCode(csdlReader, codeWriter);

                }

                if (base.CodeGeneratorProgress != null)

                {

                    base.CodeGeneratorProgress.Progress(66, 100);

                }

                if (errors != null)

                {

                    foreach (EdmSchemaError error in errors)

                    {

                        int line = (error.Line == 0) ? 0 : (error.Line - 1);

                        int column = (error.Column == 0) ? 0 : (error.Column - 1);

 

                        if (error.Severity == EdmSchemaErrorSeverity.Warning)

                        {

                            base.GeneratorWarning(0, error.Message, (uint)line, (uint)column);

                        }

                        else

                        {

                            base.GeneratorError(4, error.Message, (uint)line, (uint)column);

                        }

                    }

                }

 

                generatedCodeAsBytes = Encoding.UTF8.GetBytes(codeWriter.ToString());

            }

            if (base.CodeGeneratorProgress != null)

            {

                base.CodeGeneratorProgress.Progress(100, 100);

            }

        }

        catch (Exception e)

        {

            base.GeneratorError(4, e.Message, 1, 1);

 

            generatedCodeAsBytes = null;

        }

 

        return generatedCodeAsBytes;

    }

 

    private void OnTypeGenerated(object sender, TypeGeneratedEventArgs eventArgs)

    {

        eventArgs.AdditionalAttributes.AddRange(CreateCodeAttributes(eventArgs.TypeSource));

 

        var objectContext = eventArgs.TypeSource as EntityContainer;

        if (objectContext != null)

        {

            _objectContext = objectContext;

            var baseEntitySets = _objectContext.MetadataProperties.FirstOrDefault(mp => mp.Name == "BaseEntitySets");

            if (baseEntitySets != null)

            {

                foreach (var entitySet in (ReadOnlyMetadataCollection<EntitySetBase>)baseEntitySets.Value)

                {

                    var derivedBaseEntityType = entitySet.ElementType as EntityType;

                    if (derivedBaseEntityType != null)

                    {

                        EntitySetNames.Add(derivedBaseEntityType.Name, entitySet.Name);

                        if (_typesHierarchyToAddInObjectContext != null)

                            foreach (var derivedEntityTypeName in GetSubEntitiesOf(derivedBaseEntityType))

                            {

                                var newProp = new CodeMemberProperty { Name = derivedEntityTypeName + "s", Attributes = MemberAttributes.Public | MemberAttributes.Final, Type = new CodeTypeReference("global::System.Linq.IQueryable<" + derivedEntityTypeName + ">") };

                                newProp.GetStatements.Add(new CodeMethodReturnStatement(new CodeMethodInvokeExpression(new CodeMethodReferenceExpression(new CodePropertyReferenceExpression(new CodeThisReferenceExpression(), _entitySetNames[derivedBaseEntityType.Name]), "OfType", new CodeTypeReference(derivedEntityTypeName)))));

                                eventArgs.AdditionalMembers.Add(newProp);

                            }

                    }

                }

            }

        }

    }

 

    private void OnPropertyGenerated(object sender, PropertyGeneratedEventArgs eventArgs)

    {

        eventArgs.AdditionalAttributes.AddRange(CreateCodeAttributes(eventArgs.PropertySource));

    }

 

    private IList<CodeAttributeDeclaration> CreateCodeAttributes(MetadataItem item)

    {

        string xmlns = "http://tempuri.org/AttributeAnnotations";

 

        List<CodeAttributeDeclaration> codeAttributeDeclarations = new List<CodeAttributeDeclaration>();

        if (item != null)

        {

            IEnumerable<MetadataProperty> metadataProperties = item.MetadataProperties.Where(prop => prop.Name.StartsWith(xmlns));

            foreach (MetadataProperty metadataProperty in metadataProperties)

            {

                string metadataPropertyValue = (string)metadataProperty.Value;

                if (!String.IsNullOrEmpty(metadataPropertyValue))

                {

                    string[] attributes = metadataPropertyValue.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);

                    foreach (string attribute in attributes)

                    {

                        string attributeName = attribute;

                        string[] attributeParams = new string[1];

 

                        if (attribute.Contains('('))

                        {

                            attributeParams = attribute.Split(new char[] { '(', ')' }, StringSplitOptions.RemoveEmptyEntries);

                            attributeName = attributeParams[0];

                        }

 

                        CodeAttributeDeclaration codeAttributeDeclaration = new CodeAttributeDeclaration(attributeName);

 

                        foreach (string attributeParam in attributeParams.Skip(1))

                        {

                            object attributeParamObj = null;

                            bool attributeParamBool = false;

                            if (bool.TryParse(attributeParam, out attributeParamBool))

                            {

                                attributeParamObj = attributeParamBool;

                            }

                            else

                            {

                                attributeParamObj = attributeParam;

                            }

                            codeAttributeDeclaration.Arguments.Add(new CodeAttributeArgument(new CodePrimitiveExpression(attributeParamObj)));

                        }

                        codeAttributeDeclarations.Add(codeAttributeDeclaration);

                    }

                }

            }

        }

        return codeAttributeDeclarations;

    }

 

    private XElement ExtractCsdlContent(string inputFileContent)

    {

        XElement csdlContent = null;

        XNamespace edmxns = "http://schemas.microsoft.com/ado/2007/06/edmx";

        XNamespace edmns = "http://schemas.microsoft.com/ado/2006/04/edm";

 

        XDocument edmxDoc = XDocument.Load(new StringReader(inputFileContent));

        if (edmxDoc != null)

        {

            XElement edmxNode = edmxDoc.Element(edmxns + "Edmx");

            if (edmxNode != null)

            {

                XElement runtimeNode = edmxNode.Element(edmxns + "Runtime");

                if (runtimeNode != null)

                {

                    XElement conceptualModelsNode = runtimeNode.Element(edmxns + "ConceptualModels");

                    if (conceptualModelsNode != null)

                    {

                        csdlContent = conceptualModelsNode.Element(edmns + "Schema");

                    }

                }

            }

        }

        return csdlContent;

    }

}

How to debug a code generator used by VS?

In my case, I wanted to debug my edmx CodeGen but you can do it for a lot of other cases.

So how to debug your CodeGen?

I think there is probably a better way but this is what I did:

In my CodeGen, I put a MessageBox.Show

  • I open 2 VS: one with my CodeGen code and one with the project which will run the CodeGen
  • In the second VS, I run the CodeGen (for the edmx CodeGen, I change the Custom Tool value). The generation pauses with the MessageBox
  • I go to the first VS, I attach the second devenv process. Then I put a breakpoint after the MessageBox.Show
  • I go back to the second VS and I validate the MessageBox
  • I go back to my first projet. I am on my breapoint and I can now debug my code generator

Nice isn't it? Wink

Posted by Matthieu MEZIL | with no comments

How to have a property on ObjectContext which returns the derived entities?

With EF v1, we have an ObjectContext which has one property per EntitySet. The "problem" is that if you have an entity type Person and an entity type Employee which inherits Person, you will have only one property Persons because there is only one EntitySet Persons.

If you want to add a property Employees, you can do this:

public IQueryable<Employee> Employees

{

    get

    {

        return Persons.OfType<Employee>();

    }

}

Note that your ObjectContext generated class is partial.

Very easy!

As I said yesterday in this msdn forum thread, it is possible to customize the code generator in order to have this property directly on the generated code.

I use the SampleEdmxCodeGen project to do this:

public class SampleEdmxCodeGenerator : BaseCodeGeneratorWithSite

{

    private EntityContainer _objectContext;

    private Dictionary<string, EntitySetBase> _entitySetNames;

    private Dictionary<string, List<EntityType>> _typesHierarchyToAddInObjectContext;

    private bool _first = true;

 

    public Dictionary<string, EntitySetBase> EntitySetNames

    {

        get

        {

            if (_entitySetNames == null)

                _entitySetNames = new Dictionary<string, EntitySetBase>();

            return _entitySetNames;

        }

    }

    private Dictionary<string, List<EntityType>> TypesHierarchyToAddInObjectContext

    {

        get

        {

            if (_typesHierarchyToAddInObjectContext == null)

                _typesHierarchyToAddInObjectContext = new Dictionary<string, List<EntityType>>();

            return _typesHierarchyToAddInObjectContext;

        }

    }

    private IEnumerable<EntityType> GetSubEntitiesOf(EntityType baseType)

    {

        var empty = Enumerable.Empty<EntityType>();

        if (_typesHierarchyToAddInObjectContext == null)

            return empty;

        string baseTypeName = baseType.Name;

        if (!_typesHierarchyToAddInObjectContext.ContainsKey(baseTypeName))

            return empty;

        return TypesHierarchyToAddInObjectContext[baseTypeName];

    }

    private void AddSubEntities(EntityType baseType, EntityType typeToAdd)

    {

        string baseTypeName = baseType.Name;

        if (!TypesHierarchyToAddInObjectContext.ContainsKey(baseTypeName))

            TypesHierarchyToAddInObjectContext.Add(baseTypeName, new List<EntityType>());

        TypesHierarchyToAddInObjectContext[baseTypeName].Add(typeToAdd);

    }

 

    protected override string GetDefaultExtension()

    {

        return (".Designer" + base.GetDefaultExtension());

    }

 

    protected override byte[] GenerateCode(string inputFileContent)

    {

        byte[] generatedCodeAsBytes = null;

 

        try

        {

            XElement csdlContent = ExtractCsdlContent(inputFileContent);

            if (csdlContent == null)

            {

                throw new InvalidOperationException("No CSDL content in input file");

            }

 

            _objectContext = null;

            _entitySetNames = null;

            _typesHierarchyToAddInObjectContext = null;

 

            _first = true;

 

            // Determine language for generated code

            LanguageOption languageOption = LanguageOption.GenerateCSharpCode;

            string fileExtension = base.GetCodeProvider().FileExtension;

            if (fileExtension != null && fileExtension.Length > 0)

            {

                fileExtension = "." + fileExtension.TrimStart(".".ToCharArray());

            }

            if (fileExtension.EndsWith(".vb", StringComparison.InvariantCultureIgnoreCase))

            {

                languageOption = LanguageOption.GenerateVBCode;

            }

            else if (fileExtension.EndsWith(".cs", StringComparison.InvariantCultureIgnoreCase))

            {

                languageOption = LanguageOption.GenerateCSharpCode;

            }

            else

            {

                throw new InvalidOperationException("Unsupported project language. Only C# and VB are supported.");

            }

 

            if (base.CodeGeneratorProgress != null)

            {

                base.CodeGeneratorProgress.Progress(33, 100);

            }

 

            EntityClassGenerator classGenerator;

            IList<EdmSchemaError> errors = null;

            StringWriter codeWriter = new StringWriter(CultureInfo.InvariantCulture);

            for (int i = 0; i < 2; i++)

            {

                using (XmlReader csdlReader = csdlContent.CreateReader())

                {

                    classGenerator = new EntityClassGenerator(languageOption);

                    classGenerator.OnTypeGenerated += new TypeGeneratedEventHandler(OnTypeGenerated);

                    classGenerator.OnPropertyGenerated += new PropertyGeneratedEventHandler(OnPropertyGenerated);

 

                    errors = classGenerator.GenerateCode(csdlReader, codeWriter);

                }

                if (! _first)

                    break;

                _first = false;

                if (base.CodeGeneratorProgress != null)

                {

                    base.CodeGeneratorProgress.Progress(66, 100);

                }

                codeWriter.Dispose();

                codeWriter = new StringWriter(CultureInfo.InvariantCulture);

            }

            if (errors != null)

            {

                foreach (EdmSchemaError error in errors)

                {

                    int line = (error.Line == 0) ? 0 : (error.Line - 1);

                    int column = (error.Column == 0) ? 0 : (error.Column - 1);

 

                    if (error.Severity == EdmSchemaErrorSeverity.Warning)

                    {

                        base.GeneratorWarning(0, error.Message, (uint)line, (uint)column);

                    }

                    else

                    {

                        base.GeneratorError(4, error.Message, (uint)line, (uint)column);

                    }

                }

            }

 

            generatedCodeAsBytes = Encoding.UTF8.GetBytes(codeWriter.ToString());

            codeWriter.Dispose();

            if (base.CodeGeneratorProgress != null)

            {

                base.CodeGeneratorProgress.Progress(100, 100);

            }

        }

        catch (Exception e)

        {

            base.GeneratorError(4, e.Message, 1, 1);

 

            generatedCodeAsBytes = null;

        }

 

        return generatedCodeAsBytes;

    }

 

    private void OnTypeGenerated(object sender, TypeGeneratedEventArgs eventArgs)

    {

        var entityType = eventArgs.TypeSource as System.Data.Metadata.Edm.EntityType;

        if (_first)

        {

            if (entityType != null)

            {

                EdmType baseType;

                if ((baseType = entityType.BaseType) != null)

                {

                    //System.Windows.Forms.MessageBox.Show("");

                    while (baseType.BaseType != null)

                        baseType = baseType.BaseType;

                    var baseEntityType = baseType as EntityType;

                    AddSubEntities(baseEntityType, entityType);

                }

            }

        }

        else

        {

            eventArgs.AdditionalAttributes.AddRange(CreateCodeAttributes(eventArgs.TypeSource));

            var objectContext = eventArgs.TypeSource as EntityContainer;

            if (objectContext != null)

            {

                _objectContext = objectContext;

                var baseEntitySets = _objectContext.MetadataProperties.FirstOrDefault(mp => mp.Name == "BaseEntitySets");

                if (baseEntitySets != null)

                {

                    foreach (var entitySet in (ReadOnlyMetadataCollection<EntitySetBase>)baseEntitySets.Value)

                    {

                        var derivedBaseEntityType = entitySet.ElementType as EntityType;

                        if (derivedBaseEntityType != null)

                        {

                            EntitySetNames.Add(derivedBaseEntityType.Name, entitySet);

                            if (_typesHierarchyToAddInObjectContext != null)

                                foreach (var derivedEntityType in GetSubEntitiesOf(derivedBaseEntityType))

                                {

                                    string derivedEntityTypeName = derivedEntityType.Name;

                                    var newProp = new CodeMemberProperty { Name = derivedEntityTypeName + "s", Attributes = MemberAttributes.Public | MemberAttributes.Final, Type = new CodeTypeReference("global::System.Linq.IQueryable<" + derivedEntityTypeName + ">") };

                                    newProp.GetStatements.Add(new CodeMethodReturnStatement(new CodeMethodInvokeExpression(new CodeMethodReferenceExpression(new CodePropertyReferenceExpression(new CodeThisReferenceExpression(), _entitySetNames[derivedBaseEntityType.Name].Name), "OfType", new CodeTypeReference(derivedEntityTypeName)))));

                                    eventArgs.AdditionalMembers.Add(newProp);

                                }

                        }

                    }

                }

            }

        }

    }

 

    private void OnPropertyGenerated(object sender, PropertyGeneratedEventArgs eventArgs)

    {

        if (!_first)

            eventArgs.AdditionalAttributes.AddRange(CreateCodeAttributes(eventArgs.PropertySource));

    }

 

    private IList<CodeAttributeDeclaration> CreateCodeAttributes(MetadataItem item)

    {

        string xmlns = "http://tempuri.org/AttributeAnnotations";

 

        List<CodeAttributeDeclaration> codeAttributeDeclarations = new List<CodeAttributeDeclaration>();

        if (item != null)

        {

            IEnumerable<MetadataProperty> metadataProperties = item.MetadataProperties.Where(prop => prop.Name.StartsWith(xmlns));

            foreach (MetadataProperty metadataProperty in metadataProperties)

            {

                string metadataPropertyValue = (string)metadataProperty.Value;

                if (!String.IsNullOrEmpty(metadataPropertyValue))

                {

                    string[] attributes = metadataPropertyValue.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);

                    foreach (string attribute in attributes)

                    {

                        string attributeName = attribute;

                        string[] attributeParams = new string[1];

 

                        if (attribute.Contains('('))

                        {

                            attributeParams = attribute.Split(new char[] { '(', ')' }, StringSplitOptions.RemoveEmptyEntries);

                            attributeName = attributeParams[0];

                        }

 

                        CodeAttributeDeclaration codeAttributeDeclaration = new CodeAttributeDeclaration(attributeName);

 

                        foreach (string attributeParam in attributeParams.Skip(1))

                        {

                            object attributeParamObj = null;

                            bool attributeParamBool = false;

                            if (bool.TryParse(attributeParam, out attributeParamBool))

                            {

                                attributeParamObj = attributeParamBool;

                            }

                            else

                            {

                                attributeParamObj = attributeParam;

                            }

                            codeAttributeDeclaration.Arguments.Add(new CodeAttributeArgument(new CodePrimitiveExpression(attributeParamObj)));

                        }

                        codeAttributeDeclarations.Add(codeAttributeDeclaration);

                    }

                }

            }

        }

        return codeAttributeDeclarations;

    }

 

    private XElement ExtractCsdlContent(string inputFileContent)

    {

        XElement csdlContent = null;

        XNamespace edmxns = "http://schemas.microsoft.com/ado/2007/06/edmx";

        XNamespace edmns = "http://schemas.microsoft.com/ado/2006/04/edm";

 

        XDocument edmxDoc = XDocument.Load(new StringReader(inputFileContent));

        if (edmxDoc != null)

        {

            XElement edmxNode = edmxDoc.Element(edmxns + "Edmx");

            if (edmxNode != null)

            {

                XElement runtimeNode = edmxNode.Element(edmxns + "Runtime");

                if (runtimeNode != null)

                {

                    XElement conceptualModelsNode = runtimeNode.Element(edmxns + "ConceptualModels");

                    if (conceptualModelsNode != null)

                    {

                        csdlContent = conceptualModelsNode.Element(edmns + "Schema");

                    }

                }

            }

        }

        return csdlContent;

    }

}

The big difference between this and my previous custom generator is that, as the context is generated before the entity types, I must run the generation twice. The first to identify the sub entity types and the second to actually generate the code. 

Debug with the .Net Framework code

With VS2008, we were able to Debug with the .Net Framework code. But there weren't all the dll.

With the SP1, yes! Big Smile

Moreover, the activation of this is really easier than before. You don't have anything to do! You just need to go to the call stack, to right click on a Framework method, to go to "Load Symbols From" and then "Microsoft Symbol Servers".

Enjoy Big Smile

Posted by Matthieu MEZIL | 2 comment(s)
Filed under: ,

EF: SELECT with a SP

Imagine the following scenario. We have a SP which returns a SELECT and we want to integrate it on an EDM. For this, we will create an entity type. But we want to have to use the SP to have some instances of our entity type. This scenario isn't really covered by EF v1 and we must do some "strange things" to realize it.

For this post, we will use this SP:

CREATE PROCEDURE [dbo].[TOTO]
AS
BEGIN
 SELECT Id, 1 AS VALUE FROM Test
END

When we will get the SP on our EDM we will have this on the SSDL:

<Function Name="TOTO" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo" />

Then we will create an entity type in the CSDL (which we can do with the designer):

<EntityType Name="Toto">

    <Key>

        <PropertyRef Name="Id" />

    </Key>

    <Property Name="Id" Type="Int32" Nullable="false" />

    <Property Name="Value" Type="Int32" Nullable="true" />

</EntityType>

With the creation of the entity type, we will also have a new EntitySet:

<EntitySet Name="Totos" EntityType="TestModel.Toto" />

Then we can add the Function Import in the CSDL that will have a method on our ObjectContext. You just need to set the return type to Toto type.

Note that the entity type must have the same properties than the columns returned by the SP.

Then, as we don't want to have the EntitySet on our ObjectContext and as we can't delete it, we will set the property to private on our ObjectContext. Note that we can do this with the designer (go to Model Browser window, EntityContainer, EntitySets, Totos, go to the Properties window and change the Getter value).

But this isn't all. Indeed, we will have an error with this EDM because Toto entity type isn't mapped (even if we don't want to use it directly).

So, how to do?

I use a SSDL View. As the SQL command will never be excecuted, we can write anything (even if it isn't a correct query):

<EntityContainer Name="TestModelStoreContainer">

    <EntitySet Name="Totos" EntityType="TestModel.Store.Toto" >

        <DefiningQuery>

            I can do anything here! :-)

        </DefiningQuery>

    </EntitySet>

</EntityContainer>

<EntityType Name="Toto">

    <Key>

        <PropertyRef Name="Id"/>

    </Key>

    <Property Name="Id" Type="int" Nullable="false"/>

    <Property Name="Value" Type="int" />

</EntityType>

Then we will map the entity type on the SSDL View and here we are.

Note that EF will cover better this scenario with the V2 as Daniel Simmons told me: "In v2 we’re allowing sprocs to return complex types which will be the easy way to return objects which don’t need to be mapped, etc."

SSDL View and CUD operations

As you can see, I blog a lot on ssdl views. But what does happen when you want to CUD an entity which maps on a table and (with Entity Splitting) a SSDL view?

Fisrt if you change only the properties mapped on the columns of the table and then if you want to save your changes on DB, all is ok. Why? Because EF doesn't generate an UPDATE per table / view mapped systematically but it generates one per table / view modified.

The SSDL view are Read Only. So you will have some exceptions if you want to insert or delete an entity mapped on an SSDL View or if you want to change properties mapped on SSDL View columns and then save it.

So how can we do? In this case, you need to define yourself SSDL Functions (which can use stored procedures).

Imagine this is my SSDL before adding SSDL Functions:

<EntityContainer Name="TestModelStoreContainer">

    <EntitySet Name="Test" EntityType="TestModel.Store.Test" store:Type="Tables" Schema="dbo" />

    <EntitySet Name="StupidTest" EntityType="TestModel.Store.StupidTest" >

        <DefiningQuery>

            SELECT Id, 1 AS VALUE

            FROM Test

        </DefiningQuery>

    </EntitySet>

</EntityContainer>

<EntityType Name="Test">

    <Key>

        <PropertyRef Name="Id" />

    </Key>

    <Property Name="Id" Type="int" Nullable="false" />

    <Property Name="Name" Type="nvarchar" MaxLength="50" />

</EntityType>

<EntityType Name="StupidTest">

    <Key>

        <PropertyRef Name="Id" />

    </Key>

    <Property Name="Id" Type="int" Nullable="false" />

    <Property Name="Value" Type="int" />

</EntityType>

Test entity type has three properties:

  • Id (Key)
  • Name
  • Value (RO)

Now, I will define the three SSDL Functions:

<Function Name="InsertTest" IsComposable="false">

    <CommandText>INSERT INTO Test VALUES(@Id, @Name)</CommandText>

    <Parameter Name="Id" Type="int" Mode="In" />

    <Parameter Name="Name" Type="nvarchar" Mode="In" />

</Function>

<Function Name="UpdateTest" IsComposable="false">

    <CommandText>UPDATE Test SET Name = @Name WHERE Id = @Id</CommandText>

    <Parameter Name="Id" Type="int" Mode="In" />

    <Parameter Name="Name" Type="nvarchar" Mode="In" />

</Function>

<Function Name="DeleteTest" IsComposable="false">

    <CommandText>DELETE Test WHERE Id = @Id</CommandText>

    <Parameter Name="Id" Type="int" Mode="In" />

</Function>

And then, map them on the entity type CUD operations:

<EntitySetMapping Name="Tests">

    <EntityTypeMapping TypeName="IsTypeOf(TestModel.Test)">

        <MappingFragment StoreEntitySet="Test">

            <ScalarProperty Name="Id" ColumnName="Id" />

            <ScalarProperty Name="Name" ColumnName="Name" />

        </MappingFragment>

        <MappingFragment StoreEntitySet="StupidTest">

            <ScalarProperty Name="Value" ColumnName="Value" />

            <ScalarProperty Name="Id" ColumnName="Id" />

        </MappingFragment>

    </EntityTypeMapping>

    <EntityTypeMapping TypeName="TestModel.Test">

        <ModificationFunctionMapping>

            <InsertFunction FunctionName="TestModel.Store.InsertTest" >

                <ScalarProperty Name="Name" ParameterName="Name" />

                <ScalarProperty Name="Id" ParameterName="Id" />

            </InsertFunction>

            <UpdateFunction FunctionName="TestModel.Store.UpdateTest">

                <ScalarProperty Name="Name" ParameterName="Name" Version="Current" />

                <ScalarProperty Name="Id" ParameterName="Id" Version="Original" />

            </UpdateFunction>

            <DeleteFunction FunctionName="TestModel.Store.DeleteTest">

                <ScalarProperty Name="Id" ParameterName="Id" />

            </DeleteFunction>

        </ModificationFunctionMapping>

    </EntityTypeMapping>

</EntitySetMapping>

It is interesting to note that UPDATE doesn't include Value in its parameters but if I modify Value property, there is no problem, no request is executed in DB (which is what we wanted).

TPH on relationship

I had an EF question yesterday. The DB has two tables:

  • a table Customers
    • CustomerId (PK)
    • Name
    • CustomerTypeId (FK to CustomerTypes.CustomerTypeId)
  • a table CustomerTypes
    • CustomerTypeId (PK)
    • Description

The guy wants to keep the relationship and to have TPH inheritance on Customers.CustomerTypeId.

So this means two mappings on Customers.CustomerTypeId which is forbidden. But we can do this with ssdl view:

<EntitySet Name="CustomersCustomerType" EntityType="TestTHBModel.Store.CustomersCustomerType">

    <DefiningQuery>

        SELECT C.CustomerId, CT.CustomerTypeId

        FROM Customers AS C

        INNER JOIN CustomerTypes AS CT ON C.CustomerTypeId = CT.CustomerTypeId

    </DefiningQuery>

</EntitySet>

<EntityType Name="CustomersCustomerType">

    <Key>

        <PropertyRef Name="CustomerId" />

    </Key>

    <Property Name="CustomerId" Type="int" Nullable="false" />

    <Property Name="CustomerTypeId" Type="int" Nullable="true" />

</EntityType>

Then, we can map it with the designer:

<edmx:Mappings>

    <Mapping Space="C-S" xmlns="urn:schemas-microsoft-com:windows:storage:mapping:CS">

        <EntityContainerMapping StorageEntityContainer="TestTHBModelStoreContainer" CdmEntityContainer="TestTHBEntities">

            <EntitySetMapping Name="Customers">

                <EntityTypeMapping TypeName="IsTypeOf(TestTHBModel.Customer)">

                    <MappingFragment StoreEntitySet="Customers">

                        <ScalarProperty Name="CustomerId" ColumnName="CustomerId" />

                        <ScalarProperty Name="Name" ColumnName="Name" />

                    </MappingFragment>

                </EntityTypeMapping>

                <EntityTypeMapping TypeName="IsTypeOf(TestTHBModel.OldCustomer)">

                    <MappingFragment StoreEntitySet="CustomersCustomerType" >

                        <ScalarProperty Name="CustomerId" ColumnName="CustomerId" />

                        <Condition ColumnName="CustomerTypeId" Value="1" />

                    </MappingFragment>

                </EntityTypeMapping>

                <EntityTypeMapping TypeName="IsTypeOf(TestTHBModel.NewCustomer)">

                    <MappingFragment StoreEntitySet="CustomersCustomerType" >

                        <ScalarProperty Name="CustomerId" ColumnName="CustomerId" />

                        <Condition ColumnName="CustomerTypeId" Value="2" />

                    </MappingFragment>

                </EntityTypeMapping>

            </EntitySetMapping>

            <EntitySetMapping Name="CustomerTypes">

                <EntityTypeMapping TypeName="IsTypeOf(TestTHBModel.CustomerType)">

                    <MappingFragment StoreEntitySet="CustomerTypes">

                        <ScalarProperty Name="CustomerTypeId" ColumnName="CustomerTypeId" />

                        <ScalarProperty Name="Description" ColumnName="Description" />

                    </MappingFragment>

                </EntityTypeMapping>

            </EntitySetMapping>

            <AssociationSetMapping Name="FK_Customers_CustomerTypes" TypeName="TestTHBModel.FK_Customers_CustomerTypes" StoreEntitySet="Customers">

                <EndProperty Name="CustomerTypes">

                    <ScalarProperty Name="CustomerTypeId" ColumnName="CustomerTypeId" />

                </EndProperty>

                <EndProperty Name="Customers">

                    <ScalarProperty Name="CustomerId" ColumnName="CustomerId" />

                </EndProperty>

            </AssociationSetMapping>

        </EntityContainerMapping>

    </Mapping>

</edmx:Mappings>

This post shows you, as all the previous posts on SSDL view, the big interest of ssdl view: to make possible non-supported scenarii.

EF helps you to survive with horrible DB :-)

I had one question yesterday on the worst DataBase I have ever seen.

Imagine two tables:

  • T1 with three columns:
    • Id (PK)
    • FK1
    • FK2
  • T2 with three columns:
    • Id (PK)
    • FK1
    • FK2

And what they wanted to do is to add a relationship between T1 and T2 with T1FK1 = T2FK1 AND T1FK2 = T2FK2 using Entity Framework.

I first thought that it's a very strange way to do a relationship many to many but in fact, it's worst! The relationship they wanted is one to many from T1 to T2! (It should be probably too easy to add a T1Id column in T2 table. No comment).

Likely, Entity Framework is the friend of the developer and it is possible to do this scenario. How?

This is what I did:

I added an ssdl view:

<EntitySet Name="T1T2RelationShip" EntityType="TestLaFourmiRougeModel1.Store.T1T2RelationShipEntityType">

    <DefiningQuery>

        SELECT T1.Id AS T1ID, T2.ID AS T2ID

        FROM T1 INNER JOIN T2 ON T1.FK1 = T2.FK1 AND T1.FK2 = T2.FK2

    </DefiningQuery>

</EntitySet>

<EntityType Name="T1T2RelationShipEntityType">

    <Key>

        <PropertyRef Name="T2ID" />

    </Key>

    <Property Name="T1ID" Type="int" />

    <Property Name="T2ID" Type="int" Nullable="false" />

</EntityType>

EntityType Name="T1T2RelationShipEntityType">

Then, I added a relationship 0..1 -> many between T1 and T2 and I mapped it on my SSDL View.

That's all, folks! Smile Entity Framework is so good! Big Smile

However, in this case, I think they really should change their DB (and probably also their DBA)...

EF: prescriptive classes and IPOCO "only", what a great idea!

Some of you saw a petition against EF v1 which is a nonsense in my opinion.

I read an interesting question on the msdn forum and I think my answer illustrates why it's great to have a common base between all entities.

So of course, with POCO you can do this by using reflection and by reading the metadata but it isn't great!

Of course you can also implement yourself some common interfaces with POCO but you can forget it on one of your entity type. With IPOCO (and of course with prescriptive classes), you are sure that every entity type implements IEntityWithKey. 

Posted by Matthieu MEZIL | with no comments

Do you want to play?

Friday, I posted a quizz but I have no answer yet.

Are French the only ones who know how to use the new features of C#3? Stick out tongue

More seriously, I continue to post quizz questions on my French blog. Do you want me to post them also in English?

Posted by Matthieu MEZIL | with no comments
Filed under:

Summer Geek Quizz

Mitsu makes again his geek quizz in French which is some quizz on C# 3.0 and LINQ.

And I will do the same.

For my first quizz, the idea is to write a method which has an IEnumerable<IEnumerable<T>> parameter and which returns all the distinct elements which are on all sub-groups.

static void Main(string[] args)

{

    Console.WriteLine("Test 1");

    foreach (int i in GetElementsInAllGroups(new int[][] { new int[] { 1, 2, 3, 3 }, new int[] { 2, 2, 3, 4 } }))

        Console.WriteLine("\t{0}", i);

 

    Console.WriteLine("Test 2");

    foreach (int i in GetElementsInAllGroups(new int[][] { new int[] { 1, 2, 3, 3 }, new int[] { 2, 2, 3, 4 }, new int[]{1, 5, 6} }))

        Console.WriteLine("\t{0}", i);

 

    Console.WriteLine("Test 3");

    foreach (int i in GetElementsInAllGroups(new int[][] { new int[0] }))

        Console.WriteLine("\t{0}", i);

 

    Console.WriteLine("Test 4");

    foreach (int i in GetElementsInAllGroups(new int[][] { new int[] { 1, 2, 3, 3 }, new int[0] }))

        Console.WriteLine("\t{0}", i);

 

    Console.WriteLine("Test 5");

    foreach (int i in GetElementsInAllGroups(new int[][] { new int[] { 1, 2, 3, 3 }, null }))

        Console.WriteLine("\t{0}", i);

 

    Console.WriteLine("Test 6");

    foreach (int i in GetElementsInAllGroups(new int[][] { null, new int[] { 1, 2, 3, 3 } }))

        Console.WriteLine("\t{0}", i);

 

    Console.WriteLine("Test 7");

    foreach (int i in GetElementsInAllGroups(new int[][] { null }))

        Console.WriteLine("\t{0}", i);

 

    Console.WriteLine("Test 8");

    foreach (int i in GetElementsInAllGroups(new int[0][]))

        Console.WriteLine("\t{0}", i);

 

    Console.WriteLine("Test 9");

    foreach (int i in GetElementsInAllGroups<int>(null))

        Console.WriteLine("\t{0}", i);

}

 

public static IEnumerable<T> GetElementsInAllGroups<T>(IEnumerable<IEnumerable<T>> values)

{

    // Code it

}

The console result is:

Test 1
        2
        3
Test 2
Test 3
Test 4
Test 5
Test 6
Test 7
Test 8
Test 9

Why 2 and 3 in the Test 1? Because 2 and 3 are the only ones on {1, 2, 3, 3} and {2, 2, 3, 4}.

Enjoy Smile

Posted by Matthieu MEZIL | 2 comment(s)
Filed under: , ,

The geek attitude.

Of course there are some official definitions of geek (I have this on my MS geek T-Shirt today :-))

But, in the fact, what does a geek do?

We will suppose that I am a geek, this is what I did this night.

I was tiling my bathroom (ok this isn’t very geek). My laptop was far from the bathroom so I was very worried. :-) Moreover, I hoped that the VS 2008 SP1 was released yesterday.

When I receive an email or a msn message, my laptop beeps. I developed a program which beeps when a new post is added on a blog and I ran it on Danny’s blog, ADO.Net team’s blog and Scott Guthrie’s blog.

Then, I used a babyphone to listen to my laptop. With this, I was able to tile peacefully and to sleep well at 3:00 AM.

Posted by Matthieu MEZIL | 4 comment(s)
Filed under:

EntitySplitting: not so easy

EntitySplitting allows you to map an entity on two or more tables (see also my article on EDM).

However, there is a special case.

Imagine you have a table Employees: Id (PK), LastName, FirstName and a table Consultants: Id (PK and FK to Employees.Id).

Imagine you make software for consultants and only for them. It should be interesting to have only one entity type Consultant mapped on the Employees table and the Consultants table.

In this case, Context.Consultants returns all your employees (with the employees who aren't consultants).

When you see the SQL generated query, Consultants table isn't on the SELECT.

In fact, it's because you will have the Id with Employees table, so the INNER JOIN is removed. After asking to Sahil from the ADO .Net Team, it's the normal way.

I tried to map the Consultant entity on the Consultants table first and then on Employees table but the result is the same.

So in this case, you should use TPT.

Now imagine the following scenario: EntitySplitting with two tables with only one PK column.

In this case, we will have the content of the first mapped table.

Of course, there is no problem with INSERT which will do the insert on the two tables.

 

For information, this is the answer of Sahil:

"In EF v1 the conceptual model is _not_ a view of the DB. It portrays subset of the states of the DB such that all states can be persisted loselessly. Your conceptual model + mappings build up such constraints on the shape and value of data (i.e. subset states of the store schema) that can exist in your conceptual model - and therefore in the store when data is inserted or updated through your EF model. A trivial example of this is having a condition on a column: seelct ID from EntitySet = select ID from Table where D=5.

But also consider another scenario; TPC splitting of a hierarchy (Type1, Type2) to Table1, Table2. PKs in Table1 will never overlap with PKs in table2.

 

For this to work we could  add additional predicates to the queries so that existing data in the DB is filtered appropriately to fit the shape.

Where clause in the first example, and ANti Semi join in the second. However, in larger models these conditions get quite expensive especially when there are JOINS and ASJs required across multiple tables just to retrieve rows from a one table.

 

So instead, EF follows the closed world-model, which essentially means all data is assumed to be in a shape that can exist by updating through the given EF mapping model. In the above example Table can never carry a row with d column other than 5 so there is no need to add that predicate. Table1 and Table2 will never overlap on the PK so there is no need to check for distinct values. In your model it is not possible to have a PK in T2 that would not exist in T1 so it is safe to ignore the Inner join provided you are not projecting any properties from T2.

In certain cases you store may carry data that is unfit for the conceptual model, in which case the bahavior is undefined. IN my TPC example if you had PKs that overlap between T1 and T2 you will get a runtime exception when you try to materialize those rows. "