Insertallonsubmit Introduction to the Bulk operations of LINQ to SQL and LINQ to Object

whether LINQ to SQL or LINQ to Object (Entity frameworks), they provide the developer with insert operations, and insert collection operations, That is, InsertOnSubmit and Insertallonsubmit, the former is to mark an entity as an insert state, and then to mark a collection as an insert state, ...

Whether LINQ to SQL or LINQ to Object (Entity frameworks), they provide the developer with insert operations, and insert collection operations, InsertOnSubmit and Insertallonsubmit, The former is to mark an entity as an insert state, and then to mark a collection as an insert state, and when you do both of these, you are not connected to the database, which is the deferred load that LINQ advocates, and when do they really interact with the database? Implementation, experiments show that is to trigger the SubmitChanges method, it will be real and the database operation, this is normal, there is nothing to say.

And the main thing I'm saying today is, when we're doing bulk inserts, does the Insertallonsubmit method provided by LINQ give us the ability to do our work, and if so, whether that's the way we can accept it, we're doing an experiment.

A list:

123456789

List userList=newList();                    for(inti=0;i<100000;i++) {   userList.Add(newUser{Name="zzl"+i}); } _db.InsertAllOnSubmit(userList);                   _db.SubmitChanges();

What about the results? After my observation, the result is correct, 100,000 data can be inserted into the database, LINQ is really helping us to complete the insertion of the list, but is it acceptable to process?

To be sure, it is not possible, and is very not, for this insert operation, its pressure on the data server is amazing, it establishes a "link" 100,000 times, that is, each INSERT statement to create a link, which we can not accept, so, LINQ batch operation is really unreliable.
OK, since the way of LINQ is not advisable, then we have to do it ourselves to write, hehe, our thoughts to the 10 insert merge together, one-time to the server, one-time execution, for the current network bandwidth of these 10 data is not a problem, hehe.

Insert for a single entity, we use the deferred insertion method of LINQ:

12345

public  virtual  void  insert ( TEntity entity)   where  tentity:  class         {             db. GetTable (). InsertOnSubmit (entity);             this        }

Two batches insert the entity, we use stitching string, and send the command to the data server way, this is my more satisfied with the work, it is a common way, and do not need to modify the original insert code, its

The method signature is a list, which is correct and is very friendly to the programmer.

Look at the previous LINQ BULK INSERT:


public virtual void Insert (IEnumerable list) where Tentity:class
{
Db. GetTable (). Insertallonsubmit (list);
This. SubmitChanges ();
}
The method signature is unchanged after we modify it, so the method that originally called it does not need to be modified:


1//

2///ADO-Optimized bulk Add
3//

4//

5//

6 Public virtual void Insert

(IEnumerable list) where Tentity:class
7 {
8 this. Insertforado (list);
9}
The required auxiliary methods:


1 #region LINQ calls T-SQL implementation Bulk add
2//
3///Abstract of database tables or views
4//
5//
6//
7 metatable getmetatable (Type rowType)
8 {
9 return DB. Mapping.gettable (RowType);
10}
11
12//
13///Create SQL statement
14//
15//
16//
Tuple<string,> createinsertarguments (TEntity entity)
18 {
if (entity = = null)
throw new ArgumentException ("The database entity can is not null.");
21st
Type EntityType = entity. GetType ();
metatable table = getmetatable (EntityType);
Metadatamember identitydatamember = table. Rowtype.dbgeneratedidentitymember;
25
List arguments = new list ();
StringBuilder FieldBuilder = new StringBuilder ();
StringBuilder Valuebuilder = new StringBuilder ();
29
FieldBuilder. Append ("INSERT into" + table.) TableName + "(");
31
(Var member in table.) Rowtype.persistentdatamembers)
33 {
34
if (!member. Isassociation &&!member. isdbgenerated)
36 {
PNs Object value = Entitytype.getproperty (member. Name). GetValue (entity, NULL);
if (value! = null)
39 {
if (arguments. Count! = 0)
41 {
FieldBuilder. Append (",");
Valuebuilder. Append (",");
44}
45
FieldBuilder. Append (member. Mappedname);
if (member. Type = = typeof (String) | | Member. Type = = typeof (DateTime))
Valuebuilder. Append ("' {" + arguments. Count + "}");
$ else
Valuebuilder. Append ("{" + arguments. Count + "}");
if (value). GetType () = = typeof (String))
The value = value. ToString (). Replace ("'", "char (39)");
arguments. ADD (value);
54
55}
56}
57}
58
59
FieldBuilder. Append (") Values (");
61
FieldBuilder. Append (Valuebuilder. ToString ());
FieldBuilder. Append (");");
tuple<string,> return new (FieldBuilder. ToString (), arguments. ToArray ());
65}
66
Insertforado void (IEnumerable list)
68 {
StringBuilder sqlstr = new StringBuilder ();
List. ToList (). ForEach (I-=
71 {
tuple<string,> insert = createinsertarguments (i);
Sqlstr. AppendFormat (insert. ITEM1, insert. ITEM2);
74});
DB. ExecuteCommand (sqlstr. ToString ());
76}
77
#endregion

Insertallonsubmit Introduction to the Bulk operations of LINQ to SQL and LINQ to Object