Batch Insertion with LINQ to SQ:

Question

Hey guys,

Lets say I have a Country table and a City Table (A country has many cities reletionship),
I have these two tables droped into the LINQ to SQl designer.
What I want to do is,
Insert a country, gets the id of the inserted country object and insert a few cities.
And I was able to do this easily liek this

DataClass2DataContext context = new DataClass2DataContext();
Country country = new Country() { CountryName = "Country A" };
City city = new City() { CityName = "City 1" };
country.Cities.Add(city);
City city2 = new City() { CityName = "City 2" };
country.Cities.Add(city2);
context.Countries.InsertOnSubmit(country);

What I want to do is, insert the set of cities for a country at one shot, without hitting the database every time.
Note, that although the the example looks trivial, for my curretn requirments, the entity that is a analogous to city, would run up to around 10,000 records at once.

Any way to do this in one shot through linq to sql?

Thanx.

Answer 1

LINQ to SQL will perform all the inserts in "one shot" when you call context.SubmitChanges() - it does not hit the database in InsertOnSubmit for each.

If you set the context.Log to a stream writer (like Console.Out) you can see the TSQL it is running.

[)amien

Answer 2

hi niarooz
Table cities and countries you have filled in the other database?

Answer 3

If you this mean
i hope it could be helpfull for you 

            List<Country> CountrysReadyForInsert =new List<Country>();
          
            Country country1= new Country ();
            country1.Cities.Add(new City() { cityName= "anythings" });
            CountrysReadyForInsert .Add(country1);

             Country country2= new Country ();
            country2.Cities.Add(new City() { cityName= "anythings" });
            CountrysReadyForInsert .Add(country2);

             Country country3= new Country ();
            country3.Cities.Add(new City() { cityName= "anythings" });
            CountrysReadyForInsert .Add(country3);

            Country country4= new Country ();
            country4.Cities.Add(new City() { cityName= "anythings" });
            CountrysReadyForInsert .Add(country4);

            db.Countrys.InsertAllOnSubmit(CountrysReadyForInsert.AsEnumerable());
            db.SubmitChanges();

Answer 4

Hey guys,
Thanx for the reply.
Sorry for the confusion I caused :)
But what I really wanted to know is, when SubmitChanges() is called ..is there a way to send the sql to the database in one shot i.e as a batch statment.
Through SQL Profiler I noticed, that if I add many cities into a country object and then call SubmitChanges(), then a sql statement goes to the database one to insert the country then for each city a separate hit goes to the database.
My requirment is to find a way to send the sql statment to insert the cities as a batch "In one shot".

Any thoughts?

Answer 5

It executes all the inserts in a single transaction/batch/connection - what are you defining as "in one shot".

[)amien

Answer 6

It executes all the inserts in a single transaction/batch/connection - what are you defining as "in one shot".

[)amien

I think he means a single SQL batch. (one SqlCommand containing multiple statements).

But nope, that is not supported by L2S out-of-the-box; it will use one command per statement. Batching up a lot of statements together is not really practical with parameterized commands anyway since it will soon hit the max # of parameters limit db-side.

Batching of multiple statements can be done with some dirty tricks if you override SubmitChanges, but from a performance perspective there is so little to gain so I don't think it would be worth it...

.

---

Kristofer - Huagati Systems Co., Ltd.
Cool tools for Linq-to-SQL and Entity Framework:
huagati.com/dbmltools (add-in with new features for Visual Studio 2008's L2S and EF designers)
huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)

Answer 7

Thanx Kristofer,
That was exactly what I was refering to.