I have a LINQ-to-Entities connection to a database - how do I insert data into that database?

Question

I'd like to insert data into a database that is 1GB in size. I am currently querying the database with LINQ-to-Entities.

The problem is that the database is large - I don't want to have to pull the entire database into memory just to make an insert.

Whats your recommended method to insert data into a specific table, reasonably efficiently?

Update:

I should clarify that I'm inserting data into a table thats linked to two other tables with a one-to-many relationship. Is there a method of letting a the ADO framework handle the updating of primary/foreign key relationships?

Update:

Found the correct answer, see How do I use LINQ-to-Entities to insert data into a specific table?

Answer 1

You can use Storage Procedures beacuse of the amount of data. Or you can create a file with the data, read it line per line, and insert line per line with Linq so the data is not loaded in memory at the same time

Why should you use stored procedures? Let's take a look at the key benefits of this technology: Precompiled execution. SQL Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly. Reduced client/server traffic. If network bandwidth is a concern in your environment, you'll be happy to learn that stored procedures can reduce long SQL queries to a single line that is transmitted over the wire. Efficient reuse of code and programming abstraction. Stored procedures can be used by multiple users and client programs. If you utilize them in a planned manner, you'll find the development cycle takes less time. Enhanced security controls. You can grant users permission to execute a stored procedure independently of underlying table permissions. In MSSQL the language is Transact-SQL (T-SQL).

Answer 2

You do not need to query anything to do an insert. When using the entity framework for doing an insert, LINQ will not play a role. Use the Create... factory method of the entity you want to create. Then, add it to the entity context and call the SaveChanges() method.

For example (vb.net), if working with a table / entity called Orders and an entity context called OrderEntities:

Using context As New OrderEntities
    Dim order As Order = Order.CreateOrder(OrderNumber, Description, etc)
    context.AddToOrders(order)
    context.SaveChanges()
End Using

This will be transformed into an INSERT statement to the database.

This is a greatly simplified example. You may need to add associations, perform validation, etc. However, it illustrates that you can insert using the entity framework without executing a LINQ to Entities query first.

EDIT:

Is this table on the one side or the many side of that relationship? If it is on the one side (the primary key table), you don't need to do anything because the foreign key reference is in the other table. If it is the many side (the foreign key table), then before you call the AddTo() method, you will need to set the foreign key association.

If the foreign key is an entity loaded into the entity context, you can set it directly: order.Customer = customer

However, if it is not loaded from the database, you can set the EntityKey: order.CustomerReference.EntityKey = New EntityKey("OrderEntities.Customer", "CustomerID", 5)

Answer 3

Yes.

using(Entities context = new Entities() {
{
 Entity1 e1 = new Entity1{Id=-1, Name="E1"});
 Entity2 e2 = new Entity2{Id=-1, Name="E2", Entity1=e1});
 context.Entities2.AddObject(e2);
 context.SaveChanges();
}

I'm not sure if you have to add e1 to the Entities1 collection.

In order to this works, you have to define in your tables de Id as Identity columns.

Answer 4

The correct answer to the question is under How do I use LINQ-to-Entities to insert data into a specific table?