How to append rows to an entity table from another entity table

Question

0.00/5 (No votes)

See more:

Hi,

I have 2 entity tables, both having same structure. (bills, mast_bills).

bills contains the day sales invoices and at the end of the day, the table has to be cleared up after transferring the rows to another clone table mast_bills.

Similar to Insert into....Select from... in SQL.

What is the best way to achieve this in LINQ on Entity Tables ?

Thanks in advance.

What I have tried:

foreach(bills obj_bills in Context.bills)
{
    mast_bills obj_mast_bills = new Context.mast_bills()
    Context.mast_bills.Add{
        billno = obj_bills.billno,
        date = obj_bills.date,
        ----
        -----
     };
}

The above code is fine for smaller tables but what about tables having many columns ?

-- Similar to this can anything be done.

foreach(bills objbills in Context.bills)  
    Context.mast_bills.Add(objbills)

Posted 30-Jun-19 19:56pm

Priya-Kiko

Updated 3-Jul-19 19:58pm

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2019-07-01T00:30:00

Solution 1

Check this out: Entity Framework Bulk Copy | Christophe Geers' Blog[^]
This might be interesting too:
Entity Framework Fastest way to Insert | Entity Framework 6 Tutorial and Documentation[^]
Entity Framework: High performance querying trick using SqlBulkCopy and temp tables - Pawel Gerr's Blog[^]
Repository/EntityDataReader.cs at master · matthewschrager/Repository · GitHub[^]

Posted 1-Jul-19 0:30am

Maciej Los

Comments

Priya-Kiko 2-Jul-19 1:47am

Thanks for your response.

What Im looking for is not a fast executing logic but the way of writing code. Is there no alternative like an object instead of listing the table columns while adding the record to another entity ???

Maciej Los 2-Jul-19 3:05am

I'm sure, that using BulkCopy is the simplest and the fastest way. Every method which goes through the collection of rows (data) will be much slower than BulkCopy. Please, follow the links i've provided. There you'll find more details about that.

Priya-Kiko 2-Jul-19 6:44am

Thank you. Those links are really useful and I was able to get a lot of information there. But my query is in the below block (From one of those links)

var contacts = new List<contact>();
for (int i = 0; i < 1000; i++)
{
var entity = new Contact
{
CustomerId = Guid.NewGuid(),
FirstName = "Ruben",
LastName = "Geers",
EmailAddress = "geersch@gmail.c
};
contacts.Add(entity);
}

In the above code, the column values in my case comes from another table. Is this the only way to explicitly list out the columns even if the table has 50 columns for ex.

Priya-Kiko · Accepted Answer · 2019-07-03T19:59:00

With the help of the hint and help links posted by Maciej Los I found a solution for my question. Thank you Maciej Los.

List<bills> lstbills = Context.bills.ToList();
using (SqlBulkCopy sqlbulkCopy = new SqlBulkCopy(mconn, SqlBulkCopyOptions.Default, mtrans))
{
        sqlbulkCopy.DestinationTableName = "mast_bills";
        sqlbulkCopy.WriteToServer(lstbills.AsDataReaderOfObjects());
}

To get AsDataReaderOfObjects() extension I added BenGribaudoLLC.IEnumerableHelpers.DataReaderAdapter as a nuget package.