• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 906
  • Last Modified:

Dynamic insert LINQ query

Hi Guys,

I'm experimenting with LInq so if I'm doing something that doesn't make sense let me know.
I want to insert a bunch of (calculated) orderdetails in a seperate table so that I can make reports based upon these specific orderdetails.
I've two tables to retrieve the necessary values (this is a database what isn't created by us);
1) Orders (RBPMOU),
2) Order calculation (RBPMOU).

I created a destination table (tblSamenVoegingUitvoeringen) for testing purposels in our datawarehouse to store the results.
 
What I (want to) do is the following:
1) Select all unique orderrecords.
2) Select all ordercalculation for each individual order
3) Do calculation for each individual record and insert the result in the proper table column.

My problem is step 3 because the destination is dynamicly based upon the ordercalculation calculationID, this ID depends where the results are stored to.

How can I dynamicly change the destination field in LINQ?
The code is added below.

Tnx,
Hugo
DataClassesDataContext db = new DataClassesDataContext();

        var UniqueDebtors = (from p in db.RBPMORs
                             orderby p.ADVNR
                             select new { p.ADVNR, p.VOLGNR }).Distinct();


        foreach (var UniqueDebtor in UniqueDebtors)
        {
            var DebtorUitvoeringen = (from u in db.RBPMOUs
                                      where (u.ADVNR == UniqueDebtor.ADVNR) && (u.VOLGNR == UniqueDebtor.VOLGNR)
                                      select new { u.ADVNR, u.VOLGNR, u.PRIJS_INK, u.VOLGNR_UIT, u.UITVOERNR});
            foreach (var Uitvoering in DebtorUitvoeringen)
            {
                int myCount = 0;
                int maxUitvoering = DebtorUitvoeringen.Count();
                for (int i = myCount; i < maxUitvoering; i++)
                {
                    string myColomnName = "Uitvoering_" + Uitvoering.UITVOERNR + "_Ink";
                    // Insert with Linq.
                    
                } 
            }
        }
     }

Open in new window

0
SVBmedia
Asked:
SVBmedia
  • 3
  • 3
  • 2
  • +1
1 Solution
 
Meir RivkinFull stack Software EngineerCommented:
in the inner loop where u wrote "//Insert with Linq.", do u wish to create new column 'myColomnName' to db.RBPMOUs table?


0
 
Meir RivkinFull stack Software EngineerCommented:
and then insert the value of 'maxUitvoering' to this column?
0
 
SVBmediaAuthor Commented:
I want to do something like this:
// Insert with Linq.
// I'm just coding freely here so don't pin me down on any typo's
int calcValueUitvoering = basicPrice * Uitvoering.Percentage;

tblSamenVoegingUitvoeringen myOrder = new tblSamenVoegingUitvoeringen { DebtorNumber = Uitvoering.DebNr, OrderID = Uitvoering.OrderId, myColomnName = calcValueUitvoering };

Because the "myColomnName" doesn't exists The code files in the pre execution fase.
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
Bob LearnedCommented:
If you have a LINQ-to-SQL DataContext, and you have a table defined that you need to insert into, you can create instances of objects, call InsertOnSubmit(object), and then DataContext.SubmitChanges() to write the data to the database.
0
 
Fernando SotoCommented:
Hi SVBmedia;

You may want to re-think using the two linq queries in that way. The reason being is that the query in the foreach loop will execute a SQL query to the database which will flood the network with queries if going over a network. The code snippet reformatted the linq query to be executed once and return all the data.

When you say that the column name "myColomnName" does not exist do you mean it does not exist in the database on the server?

Fernando
DataClassesDataContext db = new DataClassesDataContext();

var DebtorUitvoeringen = (from p in (db.RBPMORs.OrderBy( p => p.ADVNR)
                                       .Select( p => new { p.ADVNR, p.VOLGNR }).Distinct())
                          from u in db.RBPMOUs                                                          
                          where (u.ADVNR == p.ADVNR) && (u.VOLGNR == p.VOLGNR)
                          orderby u.ADVNR
                          select new { u.ADVNR, u.VOLGNR, u.PRIJS_INK, u.VOLGNR_UIT, u.UITVOERNR};     

foreach (var Uitvoering in DebtorUitvoeringen)
{
    // The following two variables will need to be re-thought because now all records will be in the result set.
    int myCount = 0;
    int maxUitvoering = DebtorUitvoeringen.Count();
    for (int i = myCount; i < maxUitvoering; i++)
    {
        string myColomnName = "Uitvoering_" + Uitvoering.UITVOERNR + "_Ink";
        // Insert with Linq.
        
    } 
}

Open in new window

0
 
SVBmediaAuthor Commented:
@TheLearnedOne:
I understand how Linq inserts the results in a database, but my problem is how to dynamicly change a colomn name.

@FernandoSoto
What should I use instead of LinqToSQL? Plain old SQL query's?

The value which is created in: string myColomnName = "Uitvoering_" + Uitvoering.UITVOERNR + "_Ink";
is an existing clomn name, but the error states that myColomnName  isn't a valid colomn name
0
 
Fernando SotoCommented:
Hi SVBmedia;

You can still use Linq. The issue is that you have a string name for the column name where you need a reference to the column in the model. A way to do this is to use an extension to Linq that will allow you do dynamically reference an object by using a string. Have a look at this link:

Dynamic LINQ (Part 1: Using the LINQ Dynamic Query Library)
http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

The extension is written by Microsoft but is not part of the installed Linq Library. You will need to download it and install it on your system. The information to do that is on that page.

Fernando
0
 
SVBmediaAuthor Commented:
tnx for the link, i'll try to implement it.

I'll post the results.
0
 
Fernando SotoCommented:
Hi TheLearnedOne;

I gave SVBmedia a link to a tutorial and it directs the reader where to download the Microsoft LINQ Dynamic Query Library from which would have given SVBmedia  the tool to solve the issue.

Fernando
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now