mikaeleliasson / entityframework.utilities Goto Github PK

I have an api that bulk updates but when there are multiple requests to the api i got an error that the table already exist.

It boils down to DateTime.Now.Ticks being identical in temp table name for concurrent requests/bulk updates.

I created a custom IQueryProvider by copying SqlQueryProvider and change line 86 as below and now the same test works.

from:
var tempTableName = "temp_" + tableName + "_" + DateTime.Now.Ticks;

to:
var tempTableName = "temp_" + tableName + "_" + Guid.NewGuid().ToString("N");

Make it possible to update a table from an in memory list of item efficently.

MSSQL doesn't really have anything efficient here. You need to insert to a temp table and then merge the tables. To make that more efficient make sure that the user sets which columns to update.

MySql on the other hand can do this quite efficently from the csv files solution the use.

Preferebly it should use the same syntax as InsertAll.

Hi I am trying to update 4 columns. The data types are string. I am getting this error. I am using EF 6.1.3. First i tried with all columns I was getting similar error, then I tried with one but still without success.

System.InvalidCastException: Conversion from string " " to type 'Double' is not valid. ---> System.FormatException: Input string was not in a correct format.
at Microsoft.VisualBasic.CompilerServices.Conversions.ParseDouble(String Value, NumberFormatInfo NumberFormat)
at Microsoft.VisualBasic.CompilerServices.Conversions.ToDouble(String Value, NumberFormatInfo NumberFormat)
--- End of inner exception stack trace ---
at Microsoft.VisualBasic.CompilerServices.Conversions.ToDouble(String Value, NumberFormatInfo NumberFormat)

My Code:
EFBatchOperation.[For](poentities, poentities.LondonA).UpdateAll(list, Function(x) x.ColumnsToUpdate(Function(c) c.FullA))

Hi,

This is more a question than an issue. I was reading the article and was excited to try it out, specially about the faster Includes.

But when I dived into it I noticed that the new Include is limited and I am not sure if it is really the case of if it is me not getting the point.

Our scenario more looks like this:

context.Person.Include("Address").Include("Address.City").Include("Friends").

So I could see that for collection I can use the new Include, but i wasn't able to use it in cases where it is not a collection, say for example the address and the city.

How would i rewrite the query above with the new IncludeEFU?

Thanx,
Gili

Hi Mikael,

I'm getting this error:

Creating a DbModelBuilder or writing the EDMX from a DbContext created using Database First or Model First is not supported. EDMX can only be obtained from a Code First DbContext created without using an existing DbCompiledModel.

when using :

            foreach (var pr in products)
            {
                list.Add(new tblGoogleProduct
                    {
                        DateTimeInsertGoogle = DateTime.Now,
                        DateTimeUpdate = DateTime.Now,
                        IsForUpdate = true,
                        IsOnGoogle = true,
                        PictureId = pr.PictureId
                    });
            }

            EFBatchOperation.For(db, db.tblGoogleProducts).InsertAll(list);

Any pointers on this?

Cheers
Jon

This kind of linq-supported-include is not imported by IncludeEFU.

Plain LinqToEntity:
var firstOrDefault = db.Comments.Include(x => x.Publication.Comments.Select(comment => comment.Address)).FirstOrDefault();

With IncludeEFU:
var firstOrDefault = db.Comments.IncludeEFU(db, x => x.Publication.Comments.Select(comment => comment.Address)).FirstOrDefault();

Can I do Include children of children?

I have an object called TimeStamp that is a complex type. It has two datetime2 values.

We need to change the query building to follow the EF standards for complex types. that being [ComplexTypeName]_[ColumnName].

{"The given value of type TimeStamp from the data source cannot be converted to type datetime2 of the specified target column."}

ALTER DATABASE voder-test SET  SINGLE_USER WITH ROLLBACK IMMEDIATE;

Should be

[voder-test]

DeleteByQuery, UpdateByQuery, InsertAll, UpdateAll

Does the library allow support of other ADO providers?
We are interested in adding bulk insert for FB, API of the library is OK for us, but I can't find where we can plug-in for FB related stuff.

Because it relies on the connection type to determine the provider it fails once a connection is wrapped with for example a profiler as NHProf.

See discussion https://twitter.com/andregauci/status/502478988246523904

Possible fix: Instead of type checking the connection check the provider part of the connection string in context.Connection.ConnectionString

Today EF will throw an exception because GetDate() can't be translated to SQL if you do something like this:

Where(x => x.Date == GetDate())

This can be a bit annoying because when you simply want to use the return value as a parameter.

The question is, would you like me to provide an WhereEFU() that evaluate all in memory calls and replace them with the return values as parameter before calling the original Where()?

The current batch operation DOES support transactions by using TransactionScope. Document this.

In class MappingHelper method GetClrType

the line .Single(e => e.FullName == set.ElementType.FullName); fails when the type is not in the same namespace as the context.

set.ElementType.FullName ElementType is only a representation of the data type and as such does not give the actually assembly name as expected.

http://msdn.microsoft.com/en-us/library/system.data.metadata.edm.entitytype(v=vs.110).aspx

Reproduce by moving the models to a different namespace and run tests
InsertAll_InsertsItems
InsertAll_WithForeignKey
InsertAll_WrongColumnOrder_InsertItems
InsertAll_WrongColumnOrderAndRenamedColumn_InsertItems

Hello I have a Database First EF Model, can I use EntityFramework.Utilities?

I'm getting this error on bulk insert.

System.NotSupportedException: Creating a DbModelBuilder or writing the EDMX from a DbContext created using Database First or Model First is not supported. EDMX can only be obtained from a Code First DbContext created without using an existing DbCompiledModel.
[15500] at System.Data.Entity.Infrastructure.EdmxWriter.WriteEdmx(DbContext context, XmlWriter writer)
[15500] at EntityFramework.Utilities.EfMapping.GetEdmx(DbContext db)
[15500] at EntityFramework.Utilities.EfMapping..ctor(DbContext db)
[15500] at EntityFramework.Utilities.EfMappingFactory.GetMappingsForContext(DbContext context)
[15500] at EntityFramework.Utilities.EFBatchOperation2.InsertAll(IEnumerable1 items, DbConnection connection, Nullable`1 batchSize)

I just started playing with this Utility and it looks good so far. I am testing out some bulk updates and deletes on my SQL DB. I am batch updating a Set Tree Model (Celko Tree) and I need to update each node's Left and Right values for everything to the right of the target node. The commented out code (below) is the final result I am after. However, I am testing by setting another field so I can then go to SQL Mangler to see if the bulk update worked.

I am getting this error on the code below:

The variable name '@p__linq__0' has already been declared. Variable names must be unique within a query batch or stored procedure.

// Get all target node IDs, shift all remaining items left and delete target node(s)...
var targetList = _db.EntityTrees.Where(a => a.lft >= entity.lft && a.rgt <= entity.rgt).Select(a => a.Id).ToList();
var shiftLeft = targetList.Count * 2;
//EFBatchOperation.For(_db, _db.EntityTrees).Where(a => a.lft > entity.lft).Update(b => b.lft, b => b.lft - shiftLeft);
//EFBatchOperation.For(_db, _db.EntityTrees).Where(a => a.rgt > entity.rgt).Update(b => b.rgt, b => b.rgt - shiftLeft);
//EFBatchOperation.For(_db, _db.EntityTrees).Where(a => targetList.Contains(a.Id)).Delete();
EFBatchOperation.For(_db, _db.EntityTrees).Where(a => a.lft > entity.lft).Update(b => b.SystemId, b => shiftLeft);

_db.SaveChanges();

Since this table has over 700,000 records, I really need the bulk update! Any help is appreciated.

Is there a way to set a batch-size using this utility while BulkInserting? I am getting a timeout error when using a large dataset.

EFBatchOperation.For(context, context.KaiDeliveryWide).InsertAll(DataList);

{"The wait operation timed out"}

I am really scratching my head on this one.

I'm sorry, but there is no word about DB First in docs. I tried to save an entity and had received NotSupportedException:

var list = new List<MyEntity> { entity };
EFBatchOperation.For(context, context.MyEntities).InsertAll(list);

When I use native EF code, everything is OK:

context.MyEntities.Add(entity);
context.SaveChanges();

Exception details here:

Message:
Creating a DbModelBuilder or writing the EDMX from a DbContext created using Database First or Model First is not supported. EDMX can only be obtained from a Code First DbContext created without using an existing DbCompiledModel.

StackTrace:
at System.Data.Entity.Infrastructure.EdmxWriter.WriteEdmx(DbContext context, XmlWriter writer)
at EntityFramework.Utilities.EfMapping.GetEdmx(DbContext db)
at EntityFramework.Utilities.EfMapping..ctor(DbContext db)
at EntityFramework.Utilities.EfMappingFactory.GetMappingsForContext(DbContext context)
at EntityFramework.Utilities.EFBatchOperation2.InsertAll(IEnumerable1 items, DbConnection connection, Nullable`1 batchSize)

Is DB First supported? Thanks!

UPD: EF 6.1.1 is used

Will give an exception "The given value of type String from the data source cannot be converted to type nvarchar of the specified target column."

The temp table will create a nvarchar(1) because it ignores the maxlength,

I'm using the utility on EF 6.1.3 and Model First.
In the function EfMapping(DbContext db)
if (mapping.EntityTypeMappings.Any(m => m.IsHierarchyMapping)) is true
But
var withConditions = mapping.EntityTypeMappings.Where(m => m.Fragments[0].Conditions.Any()).ToList();
is empty.
So the next step crashes on ColumnName = ....
tableMapping.TPHConfiguration = new TPHConfiguration
{
ColumnName = withConditions.First().Fragments[0].Conditions[0].Column.Name,
Mappings = new Dictionary<Type, string>()
};
I resolved this by adding the following before this

                if (withConditions.Any())
                {

I don't know the couse of the empty list.
We don't use TPH, Just normal ForeighnKey relation.

Improvement 1: Report the batch that fails in the exception

Improvement 2: Provide a way to enable finding the exakt row. Binary search through the batch for the first row that fails for example. This is not always wanted so need to be opt-in.

So far as I can tell, this library requires your connection string to have Persist Security Info=true in order to work correctly. This is because it pulls the connection string from the context, but the context will only store the password (if using sql authentication) with that setting turned on. Might be worth mentioning in the documentation.

When trying to use IncludeEFU to include a child collection, a InvalidOperationException is thrown when enumerating the query result. The exception message is: "The property 'p' on type 't' cannot be set because the collection is already set to an EntityCollection."

This happens on line:
setter(parent, childs);
in EFQueryHelper.cs

When updating a nullable field EFU generates wrong code.

Example:
================== Generated SQL ====================
UPDATE [dbo].[customer]
SET [legalAddressId] = @p__linq__0 OR ([legalAddressId] IS NULL)
WHERE ([legalAddressId] IN
(cast('3c966889-4e55-44f3-b939-69e89f599e62' as uniqueidentifier))) AND ([legalAddressId] IS NOT NULL)
================== End Generated SQL ====================

================== Field Declaration ====================
[legalAddressId] UNIQEUIDENTIFIER NULL
================== End Field Declaration ====================

================== C# Source Code ====================
Guid? firstId;
List<Guid?> idsToDelete;
...
EFBatchOperation
.For(dataEntity, dataEntity.customer)
.Where(a => idsToDelete.Contains(a.legalAddressId))
.Update(a => a.legalAddressId, b => firstId);
================== End C# Source Code ====================

We're looking to add some redundancy to our database. We do this via triggers on raw SqlSBulkCopy usage.

However,

The EF Utilities interfaces do not supply arguments for passing this option in.

Please review my submission.

I'm using EF 6 Code First, and when I try and inserting +-1000 I'm getting the error "Object reference not set to an instance of an object.". Wonder if the issue is with handeling optional collumns.

e.g. the following model

class Person{
    int PersonId;
    int AddressId;
    Address Address;
...
}

Here is a stack trace:

at EntityFramework.Utilities.EFDataReader1.<.ctor>b__1(ColumnMapping p) at System.Linq.Enumerable.WhereSelectListIterator2.MoveNext()
at System.Collections.Generic.List1..ctor(IEnumerable1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) at EntityFramework.Utilities.EFDataReader1..ctor(IEnumerable1 items, IEnumerable1 properties)
at EntityFramework.Utilities.SqlQueryProvider.InsertItems[T](IEnumerable1 items, String schema, String tableName, IList1 properties, DbConnection storeConnection, Nullable1 batchSize) at EntityFramework.Utilities.EFBatchOperation2.InsertAll[TEntity](IEnumerable1 items, DbConnection connection, Nullable1 batchSize)

Hello !
I'm using VB.net and entity framework 6.1
I try to use the IncludeEFU.
This is the Code :

Dim MyList as IeNumerable(Of Article)
MyList= context.Articles.IncludeEFU(context, Function(t2) t2.F1_item).ToList

But I'm getting this error :
An unhandled exception of type 'System.ArgumentException' occurred in EntityFramework.Utilities.dll

Additional information: Could not find a MemberExpression

( note that the standart .Include() command works correctly ).

What can I do ?
Thank you !

First of all; great library, it works perfectly for me in almost all scenarios. The only problem I have is that I can't use UpdateAll for updating navigation properties in many-to-many relationships as seen below:

public class Character
{
    ...
    public ICollection<Book> Books { get; set; } = new List<Book>();
}

public class Book
{
    ...
    public ICollection<CharacterEntity> Characters { get; set; } = new List<Character>();
}

Character configuration:

HasMany(c => c.Books)
    .WithMany(b => b.Characters)
    .Map(cb =>
    {
        cb.MapLeftKey("BookId");
        cb.MapRightKey("CharacterId");
        cb.ToTable("BookCharacters");
    });

The following code won't update the join table with the correct ID's. I guess that I can create a stored procedure for this instead but it would be nice to have support for this in EntityFramework.Utilities or maybe I'm thinking about this in the wrong way?

characterToUpdate.Books.Add(book);

EFBatchOperation.For(context, context.Characters).UpdateAll(charactersToUpdate, x => x.ColumnsToUpdate(y => y.Books));

Precision and scale is ignored. Must add this to the column mappings.

This is a "Holy Grail" kinda enhancement request, but extending your AttachAndModifyContext to do something this would be glorious:

public void BulkUpdate<TEntity>(Expression<Func<TEntity,bool>> predicate,
            ICollection<Action<TEntity>> assignmentExpressions) where TEntity : class, new()
        {
                // ...use assignment expressions to create UPDATE statements just like 
                // Context.AttachAndModify.Set does, then reuse EF's WHERE clause 
                // generation logic to execute batch updates with no round-trip.
        }

Nice library, by the way.

Right now EFUtilities can only insert into a single table in each call. That means that an entity with child entities would have to be inserted with two call.

EFBatchOperation.For(db, db.BlogPosts).InsertAll(posts);
EFBatchOperation.For(db, db.BlogPostTags).InsertAll(posts.SelectMany(p => p.Tags));

This could be made easier. My question is how far I should take it. My personal feeling is that it's better to make an API that's simple and gives you control. Rember that these methods are there to insert really large number of items efficiently.

My thought is that it would require explicitly setting which child collections to insert.

EFBatchOperation.For(db, db.BlogPosts).InsertAll(posts,  setting => setting.Include(p => p.Tags));

Or with nesting:

EFBatchOperation.For(db, db.BlogPosts).InsertAll(posts, setting => setting.Include(p => p.Comments, setting2 => setting2.Include(c => c.Votes)));

Or with EF7 style includes:

EFBatchOperation.For(db, db.BlogPosts).InsertAll(posts, setting => setting.Include(p => p.Comments.ThenInclude(c => c.Votes)));

The other alternative is for it to always insert the whole object graph automatically. Like the DbContext does today.

EFBatchOperation.For(db, db.BlogPosts).InsertAll(posts);
//Magic happens and everything is saved

So what does people prefer? Would you like to be able to control exactly what goes in or would you prefer the automagic solution?

Some notes:

• This will work even with db generated id's. I found a solution to get the id's back even when using SqlBulkCopy.
• That "setting" is more than for just includes. It supports some other parameters too and will be used for future options/enhancements. sSo even if it looks uglier it gives more flexibility. For example because different entities have different "byte size" the BatchSize should probably be configured differently for optimal performance.

I am working on a process to insert data from many flat files, my process uses TPL to insert simultaneously, I am getting various timeout exceptions randomly. My SQL Server version is 2005.

System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket() at System.Data.SqlClient.TdsParserStateObject.ReadByte() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal() at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount) at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader) at EntityFramework.Utilities.SqlQueryProvider.InsertItems[T](IEnumerable1 items, String schema, String tableName, IList1 properties, DbConnection storeConnection, Nullable1 batchSize) at EntityFramework.Utilities.EFBatchOperation2.InsertAll[TEntity](IEnumerable1 items, DbConnection connection, Nullable1 batchSize) at MyProject.FileProcessor.Process(List`1 parameters)

I have tried setting the CommandTimeout property from EF DataContext to 0, with no luck.

What is the default value the library is using for the BulkCopyTimeout property?

Hi, im getting the "Sequence contains no matching element" when there is no entries in the included collection. Have tried to add DefaultIfEmpty with no luck. How should the query look like? Im selecting into a new class.

[InvalidOperationException: Sequence contains no matching element]
System.Linq.Enumerable.First(IEnumerable1 source, Func2 predicate) +482
EntityFramework.Utilities.EFQueryHelpers.GetFKProperty(ReadOnlyCollection1 cSpaceTables) +334 EntityFramework.Utilities.EFQueryHelpers.GetForeignKeyGetter(ReadOnlyCollection1 cSpaceTables) +59
EntityFramework.Utilities.EFQueryHelpers.IncludeEFU(IQueryable1 query, DbContext context, Expression1 collectionSelector) +368

Thanks in advance!

The discriminator column causes problems and it throws a NullReferenceException.

Hi, first of all thanks for this utilities over EF because you saved my ass today. After this huge thank you, begins the bugs ;)

I had two problems. First, at the MappingHelper line 122:

var mappingFragments = edmx
                    .Descendants()
                    .Single(e =>
                        e.Name.LocalName == "EntityTypeMapping"
                        && (e.Attribute("TypeName").Value == set.ElementType.FullName || e.Attribute("TypeName").Value == string.Format("IsTypeOf({0})", set.ElementType.FullName)))
                    .Descendants()
                    .Where(e => e.Name.LocalName == "MappingFragment");

I had a crash because the .Single found two mappings with the next structure:

<EntityTypeMapping TypeName="Altai.Evolution.Laboral.Infraestructure.Data.GrupoTarifa" xmlns="http://schemas.microsoft.com/ado/2009/11/mapping/cs">
  <MappingFragment StoreEntitySet="GrupoTarifa">
    <ScalarProperty Name="Id" ColumnName="Id" />
    <Condition Value="GrupoTarifa" ColumnName="Discriminator" />
  </MappingFragment>
</EntityTypeMapping>

I don't understand very well the code of this section, but I fixed my problem changing the .Single for a .First call. This isn't a truely fix, but I didn't know why the fuck there were two matches for that .Single call (having in mind that I didn't understand very well what does that method).

The second problem was more tricky. When I made this call:

EFBatchOperation.For(context, context.Set<Poblacion>())
                        .Where(x => x.Codigo.StartsWith(provincia.Codigo))
                        .Update(pob => pob.Provincia.Id, poblacion => provincia.Id);

There was an exception with the message "The variable name '@p__linq__0' has already been declared. Variable names must be unique within a query batch or stored procedure."

Debugging, I found that at the concatenation of parameters of the method Update of class EFBatchOperation (line 159) there were two params called "p__linq__0" because at the 'queryInformation' var there was one parameter for the 'where' clause of the query called "p_linq_0" and at 'mqueryInfo' var there was another with the same name for assigning the value. That caused the error.

For fixing it, I renamed the duplicated param at the update clause with the next code:

        public int Update<TP>(Expression<Func<T, TP>> prop, Expression<Func<T, TP>> modifier)
        {
            var con = context.Connection as EntityConnection;
            if (con == null)
            {
                Configuration.Log("No provider could be found because the Connection didn't implement System.Data.EntityClient.EntityConnection");
                return Fallbacks.DefaultUpdate(context, this.predicate, prop, modifier);
            }

            var provider = Configuration.Providers.FirstOrDefault(p => p.CanHandle(con.StoreConnection));
            if (provider != null && provider.CanUpdate)
            {
                var set = context.CreateObjectSet<T>();

                var query = (ObjectQuery<T>)set.Where(this.predicate);
                var queryInformation = provider.GetQueryInformation<T>(query);

                var updateExpression = ExpressionHelper.CombineExpressions<T, TP>(prop, modifier);

                var mquery = ((ObjectQuery<T>)context.CreateObjectSet<T>().Where(updateExpression));
                var mqueryInfo = provider.GetQueryInformation<T>(mquery);

                List<ObjectParameter> mqueryParams = GetFixedParams(query, mquery, mqueryInfo);

                var update = provider.GetUpdateQuery(queryInformation, mqueryInfo);

                var parameters = query.Parameters
                    .Concat(mqueryParams)
                    .Select(p => new SqlParameter { Value = p.Value, ParameterName = p.Name }).ToArray<object>();

                return context.ExecuteStoreCommand(update, parameters);
            }
            else
            {
                Configuration.Log("Found provider: " + (provider == null ? "[]" : provider.GetType().Name) + " for " + con.StoreConnection.GetType().Name);
                return Fallbacks.DefaultUpdate(context, this.predicate, prop, modifier);
            }
        }

        private List<ObjectParameter> GetFixedParams(ObjectQuery<T> query, ObjectQuery<T> mquery, QueryInformation mqueryInfo)
        {
            List<ObjectParameter> paramsFixed = new List<ObjectParameter>();

            foreach (ObjectParameter parameter in mquery.Parameters)
            {
                int counter = 1;
                string name = parameter.Name;

                ObjectParameter clonedParameter = new ObjectParameter(parameter.Name, parameter.Value);

                if (query.Parameters.ToList().Exists(x => x.Name == clonedParameter.Name))
                {
                    while (query.Parameters.ToList().Exists(x => x.Name == clonedParameter.Name))
                    {
                        clonedParameter = new ObjectParameter(name + "_" + counter, parameter.Value);
                    }

                    mqueryInfo.WhereSql = mqueryInfo.WhereSql.Replace(name, clonedParameter.Name);
                }

                paramsFixed.Add(clonedParameter);
            }

            return paramsFixed;
        }

I'm sure that it's not the right way to fix it, but I couldn't find another way to fix both problems. You will judge if there were real bugs and if they were, I can upload the fixes here to update the nuget package.

Don't know if I explained the problems clearly, if you need more info just ask for it. Thanks again and best regards!

I'm trying this library but I'm getting this exception stack:

System.Core.dll!System.Linq.Enumerable.First<System.Data.Entity.Core.Mapping.EntityTypeMapping>(System.Collections.Generic.IEnumerable<System.Data.Entity.Core.Mapping.EntityTypeMapping> source)
EntityFramework.Utilities.dll!EntityFramework.Utilities.EfMapping.EfMapping(System.Data.Entity.DbContext db)
EntityFramework.Utilities.dll!EntityFramework.Utilities.EfMappingFactory.GetMappingsForContext(System.Data.Entity.DbContext context)
EntityFramework.Utilities.dll!EntityFramework.Utilities.EFBatchOperation<System.Data.Entity.DbContext, SoftFin.Snapshots.SBalance>.InsertAll<SoftFin.Snapshots.SBalance>(System.Collections.Generic.IEnumerable<SoftFin.Snapshots.SBalance> items, System.Data.Common.DbConnection connection, int? batchSize)

I presume it is this line https://github.com/MikaelEliasson/EntityFramework.Utilities/blob/master/EntityFramework.Utilities/EntityFramework.Utilities/MappingHelper.cs#L154 .

So, I guess my model is somehow incompatible with EFUtilities. Why? Is there a way I can correct fix this?

An exception of type 'System.InvalidOperationException' occurred in EntityFramework.Utilities.dll but was not handled in user code

Additional information: No provider supporting the InsertAll operation for this datasource was found.

When trying to insertAll in VB.net EF 6.1.3 EFUtilities 1.0.2

Any idea on where to look to get this working?

While looking through the code I found that the following chunk of code:

var con = context.Connection as EntityConnection;
if (con == null && connection == null)
{
    Configuration.Log("No provider could be found because the Connection didn't implement System.Data.EntityClient.EntityConnection");
    Fallbacks.DefaultInsertAll(context, items);
}

was just copy and pasted from InsertAll to UpdateAll providing the wrong default fallback for UpdateAll

Additionally on the if (provider != null && provider.CanBulkUpdate) the same fall back is in the else statement.

i want to know license of this library.

but license file does not exist.
LICENSE.txt (or LICENSE.md)

When writing data import for large sets of data I often find myself writing something along the lines of:

var sourceOrders = sourceContext.Orders.ToList();
var mappedOrders = MapToTarget(sourceOrders);
targetContext.Orders.AddRange(mappedOrders);
targetContext.SaveChanges();

Where MapToTarget maps the top level entity (Order) and all the nested entities underneath (for instance, Customer, OrderLine, Product, ...). In the end you AddRange and SaveChanges, and expect EF to graciously handle the import operation of a couple of thousand entities.

I don't need to tell you how wrong the above assumption is.

One of the ways BatchInsert/BulkInsert can help here, is that if you know the dependency structure of the Entities, you can flatten the graph into 4 sets: Customers, Orders, Products and OrderLines, and insert them in the reverse order of dependency. Root to leaf node.

var sourceOrders = sourceContext.Orders.ToList();
var mappedOrders = MapToTarget(sourceOrders);
EFBatchOperation.For(targetContext, targetContext.Customers).InsertAll(mappedOrders.Select(o => o.Customer));
EFBatchOperation.For(targetContext, targetContext.Orders).InsertAll(mappedOrders);
EFBatchOperation.For(targetContext, targetContext.Products).InsertAll(mappedOrders.SelectMany(o => o.OrderLines.Select(ol => ol.Product)));
EFBatchOperation.For(targetContext, targetContext.OrderLines).InsertAll(mappedOrders.SelectMany(o => o.OrderLines));

I have found that in complex object models where the dependency structure might not be super clear, it can help to have a library assist you in topologically sorting the added entity sets by their dependency, and then bulk inserting the ordered sets.

The idea is that given that you have an acyclic dependency graph between your entities, you can leverage the EF changetracker to do the heavy lifting in terms of figuring out what has changed, and what the dependencies are between the changes, in order to then save the changes via bulkinsert:

// Just add them to the "Orders" DbSet, and have EF handle figuring out what has changed
targetContext.Orders.AddRange(mappedOrders);
// ... then, using the ObjectStateManager, and the metadata we have about the dependency graph, sort, and InsertAll
targetContext.SaveAdditions();

SaveAdditions would first verify that the ObjectStateManager only contains added entities (not modified or deleted ones).
Then you would topologically sort based on their dependency.
Then you could bulk insert in decending order of dependency: Customer > Order > Product > OrderLine.

I have an implementation of this based on a different BulkInsert package: https://github.com/ymotton/EntityFramework.BulkExtensions/blob/master/EntityFramework.BulkExtensions/DbContextBulkExtensions.cs

Hi!

I would appreciate some feedback on the UpdateAll method. If you are not sure what it does it takes the c# like:

db.UpdateAll<BlogPost>(b => b.Title == "T2", b => b.Reads - 5)

and generates a sql query from that like:

exec sp_executesql N'UPDATE [dbo].[BlogPosts] SET [Reads] = Reads - @update_1 WHERE (N''T2'' = [Title]) AND ([Title] IS NOT NULL)',N'@update_1 int',@update_1=5

Question 1

First of all I'm thinking about renaming it to UpdateByQuery because that's really what it does. UpdateAll would in my mind be a better name for a method that took x number of entities and batched the update statement or just somehow updated them all.

This would also mean DeleteAll would be renamed to DeleteByQuery.

Does this seem like a better name?

Question 2

The format above means the update statement has to be manually parsed an built. Adding support for updates other than the basic operations like math and string concantenation requires manual translation.

Another approach is to make the API look like this which make it possible to trick EF into building the sql:

db.UpdateByQuery<RenamedAndReorderedBlogPost>(b => b.Title == "T2", b => b.Created == DbFunctions.AddDays(b.Created, 1))

Note that the first expression is the predicate and the second expression is the update statement. I don't like that it's an == instead of an assignment but assignements are not allowed in expression trees.

By doing it this way it's fairly straightforward to support much more complex updates like assignment based on other properties, DbFunctions etc etc. Do you think this is an ok trade off?

The current implementation of the batch update can't update tables that reside in a different schema than "dbo" (and this is confirmed by the SqlQueryProvider implementation)

Hello !

I want to know how is possible to create a such scenario with your tool : ( I have described the conditions with words , to better understand what I want )

ParentObject - has 2 child entities Child1 and Child2.

IQueryable MyList;

Mylist = Context.Set;

If condition1 ...."From Mylist Select the ParentObjects with ".vl1=1 ..."

If Condition2 ..."From Mylist select the ParentObjects with ".id>=5 ..."

If Condition3 ....For all the ParentObjects in MyList , Include the items on Child1 that have ".quantity>3"....

If condition4 ....For all the ParentObjects in MyList , from the items on Child1 ( of course form all or from those that have been included in condition3 if it was true ) include only those that have ".value5=0....

If condition5 ....For all the ParentObjects in MyList , Include the items from Child2 that have .type=3.....

MyList.Tolist()

The true problem is on Condition 3 , 4 and 5 , that I can't do with Entity framework because has no Include that support filters. I hope that your tool will help me.

What can I do ?

Thank you !

Right now a call like this:

EFBatchOperation.For(db, db.InvoicePayments).Where(x => x.Invoice.OrgNumber == c.OrgNumber).Delete();

Result in exception about a missing alias because the alias is not properly defined when grabbing the WHERE part from the query.

Investigate if this can be fixed or provide a better exception.

I am using EF.Utilities 0.5.0 in my code and have some query regarding it.
First of all, this is the best package that I have come across for performing bulk operations.
Now about my query, I have a condition in my code where I am trying to delete an entire table (around 24K records) and then add same number of records again.
For deletion, I have added following code:
EFBatchOperation.For(context, context.ArticleImages).Where(x => true).Delete();
And for insertion, I am using following code:
EFBatchOperation.For(context, context.ArticleImages).InsertAll(articleImages);

I am doing this in a single method and passing same context object in both calls.
Now, I want to include these both operations in a single TransactioScope. Please suggest how should I do it?
Thanks in advance.

I want to set an FK column to null.

EFBatchOperation.For(context, context.Table)
.Where(a => a.FKColumn == id).Update(a => a.FKColumn , p => null);

Output:

UPDATE [dbo].[Table] SET [FKColumn] IS NULL WHERE [FKColumn] = @p__linq__0

Instead of "IS", it should output "=".

Any work on EF.Core support ? Perhaps I can assist if nobody has taken it up yet.

I know its not a final, but EF7 has very nice features and I'd like to have bulk insert support :)
Most of the work will probably be refactoring the namespace :')

The Method UpdateAll on Table that have 2 columns Primary Key has generated a query like this:

UPDATE [StudyAssumption]
SET
[IsSponsor] = TEMP.[IsSponsor],[IsCromsource] = TEMP.[IsCromsource]
FROM
[StudyAssumption] ORIG
INNER JOIN
[temp_StudyAssumption_635693559224566926] TEMP
ON
ORIG.[IdStudy] = TEMP.[IdStudy], ORIG.[IdAssumptionType] = TEMP.[IdAssumptionType]

There is a "," instead an "AND in the ON Clause that results in a SQL Server error