Batch Insert with Entity Framework – LINQ to Entity

Batch Insert with Entity Framework – LINQ to Entity

Entity Framework – LINQ to Entity is a good idea for batch insert? Such as more than one thousand records.

When you try to do batch operations, you should be carefully using not only Entity Framework but also most of the ORM tools. The main problem is the context lifetime and according to that memory leaks. Context acts as a cache and in batch operations the size of context increases very quickly and that causes fatal errors or makes the speed of the operations too slow that is unacceptable!

If you use any ORM tools, it is not a good idea using that in batch operation (if it does not have a specific module). For the Entity Framework: After inserting about 1000 records, Entity Framework cause a memory leak and for the 20 0r 30 thousand of records the operation never ends.

You maybe divide the records in to small parts and after inserting each part you can create a new context instance and dispose the previous to avoid  memory leak but the speed! However, if you want to use transaction, you cannot divide the records.

{

int counter = 0;

using (MyEntities dataModel = new MyEntities ())

{

while (counter < 1000)

{

//Insert

counter++;

}

}

}

For now the best way for batch operations is; not using the Entity Framework. There are lots of ways to do batch operations without Entity Framework. One of them is using SqlBulkCopy or using native Ado.Net classes.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx

I hope that ORM tools and Entity Framework improve themselves in this side.

LINQ – Copy an Entity in Different Contexts

Error:

“An entity object cannot be referenced by multiple instances of IEntityChangeTracker.”

Solution:

“ApplyCurrentValues” method. (For Framework 3.5 “ApplyPropertyChanges”)

This solution is might be useful if you want to copy the scalar values of an entity to another entity that is belong to a different context.

For example I have a method that takes an invoice entity and updates it.

public bool UpdateInvoice(InvoiceEntity invoice)

{

int res = 0;

try

{

using (MyEntities dataModel = new MyEntities ())

{

var originalRecord = (from p in dataModel.InvoiceEntity

where p.InvoiceId == invoice.InvoiceId

select p).FirstOrDefault();

dataModel.ApplyCurrentValues(originalRecord.EntityKey.EntitySetName, invoice);

res = _dataModel.SaveChanges();

}

}

catch (Exception ex)

{

//Logging

throw ex;

}

return res > 0;

}

If you wish, Instead of this method you can create a new instance of the invoice and asign the values using the invoice in the method parameters. However, you have to update that code whenever the invoice table updated.

The table/view does not have a primary key defined

Error:

The table/view does not have a primary key defined and no valid primary key could be inferred. This table/view has been excluded. To use the entity you will need to review your schema, add the correct keys and uncomment it.

Solutions:

The framework requires each entity to have a key. If no key is defined in the in the database object (like a view, or, as in your case, a table with no key), the tools try to infer a key based on the columns of the table or view (at least one non-binary, non-nullable column is required to infer a key). When no key can be inferred, a code comment that contains the corresponding EntityType element (with no Key elements) is added to the SSDL section of the .edmx file.

There are two solutions:

First Solution:

In your case, since it seems that you want a read only entity, you could…

1) Uncomment the SSDL entity

2) Mark one/some properties as Nullable=”False”

3) Add the appropriate Key elements

4) Add a corresponding defining query.

More detail in this forum thread about how to add a defining query.

Hope that helps.

Brian

Second Solution:

Insert to view a non-nullable column. You can use row number as a new column or modify the one of the non-binary current columns as a non-nullable. You can use ISNULL function to make the column as non-nullable.

Using rownumber as a new column:

SELECT ISNULL((ROW_NUMBER() OVER (ORDER BY YEAR DESC)), 0) AS ‘ID’, CITYNAME, YEAR, BRAND, SUM(SUMCOUNT) AS SALES

FROM         dbo.V_CITY_SALES_SUM

WHERE     (PRODUCT = ‘MOTORCYCLE’)

GROUP BY CITYNAME, YEAR, BRAND

If there is a “group by” clause do not make the grouped columns as a non-nullable column. It causes the all rows returned from the entity being the same!

Comments:

In the first solution you need to modify the schema but not the view and in every update you will lost the handmade modifications in the schema. In the second solution you need to modify the view only one time but not the schema so it seems better.

RDL Rapor Dosyalarında RDL 2008 Özelliklerini Kullanımı Hatası

Alınan hata:

The report definition is not valid.
Details: The report definition has an invalid target namespace ‘http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition’ which cannot be upgraded.

Çözüm:

Bu hatayı alıyorsanız hatanın muhtemel sebebi: RDL 2008 özelliklerini kullanan bir raporunuz var ancak raporları görüntüleme için kullandığınız Report Viewer’iniz RDL 2008 i desteklemiyordur.

Visual Studio 2008 çıktığı tarihlerde SQL Server 2008 yayınlanmamıştı ve bu yüzden VS 2008, RDL 2008 i desteklememektedir. Yapmanız gereken viewer i yükseltmek (VS 2010 /framework 4.0 kullanmak) yada raporunuzu “local mod” da değil “server mod” da çalıştırmak olacaktır.

Eğer VS 2010 ile çalıştığınız halde aynı hatayı alıyorsanız ve çalıştığınız proje önceki VS sürümlerinden dönüştürülmüş ise Report Viewer’in referansları değişmemiş demektir. Report Viewer’i kullandığınız yerden silip yeni bir tane ekleyin (yeni Report Viewer eklenecektir.). Bu arada referanslar bölümünden de Report Viewer’in eski sürüme ait referansları temizleyin.