RSS

Tag Archives: Entity Framework

Unable to update the EntitySet because no <InsertFunction> element existing

Today I received a strange error when saving a newly created entity into the database.

Unable to update the EntitySet ‘EntityName’ because it has a DefiningQuery and no <InsertFunction> element exists in the element to support the current operation.

Well this is strange. The EntityName is a table in SQL and there should be no problems in inserting a new entry by using conn.Set<EntityName>().Create().

Looking at the SQL script to create the table, I realized that the problem was, that no Primary Key has been defined for the table. Therefore Entity Framework decided that EntityName should be traced as a View. Adding a primary key solved the above shown issue.

 
Leave a comment

Posted by on May 28, 2015 in C-Sharp, EF, SQL

 

Tags: , ,

Create EDMX-file while applying Code First

Code first is a very nice feature, especially when you use the migration feature, but what if you are applying the MVVM pattern and want to generate view models from your code first model classes? This can be achieved with T4 templates. But a model generating T4 template, such as that one that generates POCO-classes from an edmx file, requires an edmx file. But when you are using code first this file is, by design, missing. So we need a way to generate an edmx file. This can be achieved with a single static method provided by the Entity Framework. The method of interest is EdmxWriter.WriteEdmx. You can use this method as follows:

var dbContext = new MyDbContext();
var xmlWriter = new XmlTextWriter("OUTPUT-PATH", new UTF8Encoding());
EdmxWriter.WriteEdmx(dbContext,xmlWriter);

After that an EDMX-file is generated to the specified output-location which can then be used as input file for T4 templates.

 
Leave a comment

Posted by on March 29, 2013 in EF

 

Tags: , ,

Object mapping could not be found for Type with identity {X}

If you try executing TryGetObjectByKey and you get the error above, you have missed to set the MetadataWorkspace in your ObjectContext.
This is achieved by doing the following:

Assembly a = typeof(MyContext).Assembly;
//ctx is the ObjectContext
ctx.MetadataWorkspace.LoadFromAssmebly(a);

After that you will be able to execute TryGetObjectByKey or other methods without getting exceptions.

 
Leave a comment

Posted by on May 2, 2012 in C-Sharp, EF

 

Tags:

IEnumerable vs. IQueryable

When you are heavily working with the Entity Framework using LINQ you have certainly touched both interfaces. In one hand IEnumerable and on the other hand IQueryable. Looking at their inheritance tree you could see following:

IEnumerable
-IEnumerable
–IQueryable

So what is the great difference between both of them. According the interface declaration they implement the same methods, therefore thus you can think that the methods work the same way. But the reality is far away of them.

To test my apprehension that both of them do not call the same SQL-Command on the server i constructed following:

public void IEnumerableTestMethod(IEnumerable coll )
{
    int count = coll.Where(d=>d.Kolli > 0).Count();
}

public void IQueryableTestMethod(IQueryable coll)
{
    int count = coll.Where(d => d.Kolli > 0).Count();
}

and I made the call following:

IQueryable sentItems= DBSetting.ObjectContext.DeliveryItem.Where(d=>d.Pieces > > 100);
IQueryableTestMethod(sentItems);
IEnumerableTestMethod(sentItems);

Both methods were called with the same IQueryable as input. Also the method body are exactly the same. The only and only difference is the type of parameter that was used. In the first method called IEnumerableTestMethod the type of parameter is IEnumerable<T> the second one called IQueryableTestMethod IQueryable<T>.

Now, at first sight this is the only difference. I agree with you.

But if you are going to start testing what kind of SQL-Statements were executed against the SQL-Server using IntelliTrace you will see following result:

IQueryableTestMethod:

SELECT
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
COUNT(1) AS [A1]
FROM [dbo].[DeliveryItem] AS [Extent1]
WHERE ([Extent1].[Pieces ] > 100) AND ([Extent1].[Kolli] > 0)
) AS [GroupBy1]

IEnumerableTestMethod:

SELECT
[Extent1].[Id] AS [Id],
[Extent1].[DeliveryNoteId] AS [DeliveryNoteId],
[Extent1].[OrderId] AS [OrderId],
[Extent1].[Pieces] AS [Pieces],
[...continuing all other columns of DeliveryItem...]
FROM [dbo].[DeliveryItem] AS [Extent1]
WHERE [Extent1].[Pieces] > 100

So if you be aware you see the big difference. Ignoring the fact that the second query returns a SELECT * of all filtered DeliveryItem you see at first sight that in the second query the COUNT is missing. Now the question is if this is bad or not. Obviously this is bad, but why?

The first query returns only the number of items that matches the query, but the second query returns all data that matches the condition and computes the number of items on the client-side. This might be a huge performance issue if the returning data set is getting bigger and bigger.

If you have IEnumerable<T> as paramenter the first method executed on that collection will create the SQL-Statement that is executed against the SQL-Server. Therefore having the statement coll.Where(a => a.Kolli > 0).Where(a => a.Kolli < 0).Count(); (unnoticed that it is useless) will not change the query in IEnumerable but using IQueryable there will be the change in the WHERE condition

But if you have a IEnumerable parameter you have another possibility, using AsQueryable, to speed up the query and not to load all data.

This can be done following:

//#1
public void IEnumerableTestMethod(IEnumerable coll )
{
    int count = coll.AsQueryable().Where(a => a.Kolli > 0).Count();
}

Then you will get the same SQL-Query like in the IQueryalbeTestMethod, but be aware of using AsQueryable, using it following will have no improvement of the SQL-Query, it still remains like the SQL-Query you will get in the original IEnumerableTestMethod:

//#2
public void IEnumerableTestMethod(IEnumerable coll )
{
    int count = coll.Where(a => a.Kolli > 0).AsQueryable().Count();
}

In this solution the Where Extension method already executes the above showed SQL-Statement against the SQL-Server and the AsQueryable has no effect.

Also be aware, because if you are using the method seen in #1 and you call it trough following example you are fethching all data from the server and you have no improvement of the SQL-Query.

var list = collection.ToList();
//now you have loaded the data via a "SELECT * FROM Table"
IEnumerableTestMethos(list);

In conclusion I want to say that using IQueryable and IEnumerableis very risky if you just write IEnumerable<T> as method parameter. You should always be aware and use as much as possible the IQueryable<T>.

 
1 Comment

Posted by on August 7, 2011 in EF, LINQ, SQL

 

Tags: , , ,