We help IT Professionals succeed at work.

Build dynamic LINQ Query over Datatables

5,025 Views
Last Modified: 2013-11-11
Below is LINQ query which returns the New records which exist in dtFreshdata  but not in dtSnapShot.
My question is that
how to generalize this code so that it works for all DataTables of different schema
In this query I hard coded column name but I want to construct it dynamically.
and I have more than one Constraint column and i want Group join ON more than one constraints.

for e.g string[] strArrConstraints contains the constraints.

I hope You people come out with a solution ASAP.
Dim objquery = From FreshData In dtFreshData.AsEnumerable() _
                           Group Join SnapShotData In dtSnapShot.AsEnumerable() _
                 On FreshData("ACCTID") Equals SnapShotData("ACCTID") Into HaveMatch = Any() _
            Where Not HaveMatch _
            Select ACCTID = FreshData("ACCTID"), ACCTNAME = FreshData("ACCTNAME"), ACCTADDRESS = FreshData("ACCTADDRESS")

Open in new window

Comment
Watch Question

what is HaveMatch?

Ignore my last question.

Something like this will work, but you have to keep in mind that for your select, you have to specify *explicitly* what the variable names will be or you will get an error like this:
Range variable name can be inferred only from a simple or qualified name with no arguments

So that can be be set, but that would be static.  Otherwise, like I have it, you will return the full rows and have to access them through a little different method.
Function getNewUnique(ByVal dtFreshData As DataTable, ByVal dtSnapShot As DataTable, ByVal strFilter As String) As IEnumerable(Of DataRow)
  Dim query = From FreshData In dtFreshData.AsEnumerable() _
    Group Join SnapShotData In dtSnapShot.AsEnumerable() _
    On FreshData(strFilter) Equals SnapShotData(strFilter) Into HaveMatch = Any() _
    Where Not HaveMatch _
    Select FreshData
  Return query
End Function

Open in new window

Author

Commented:
naspinski
I  am a beginner on linq. please tell me how to give constraints in strFilter
strFilter is simply a string which would be the column name from the datatable that you wish to sort with.  In your query it is simply "ACCTID"

So  to call this you would do something like this:
Dim query = getNewUnique(dtFreshData, dtSnapShot, "ACCTID")
 
'and that will give you an IEnumerable list of datarows'

Open in new window

Author

Commented:
naspinski

i told in my question if i have multiple contraints then what will be dynamic query.

for e.g if if i have ACCTID and ACCTNAME as constraint. it is just eg i my have more than 1 or 2 or 3 constraints
What exactly do you mean by more constraints?  Where will they be?  The dynamic ability of linq is limited in some areas.

Author

Commented:
I am filling dtfreshdata at runtime it may any database table.
 I have constraints in string array. in above eg
if i have ACCTID and ACCTANAME as constraints column.

Dim strArrConstraints as string() = {"ACCTID, "ACCTNAME""};
now based on this strArrConstraints i want  query which will be converted like this while executing....

Dim objquery = From FreshData In dtFreshData.AsEnumerable() _
                           Group Join SnapShotData In dtSnapShot.AsEnumerable() _
                 On FreshData("ACCTID") Equals SnapShotData("ACCTID") And FreshData("ACCTNAME") Equals SnapShotData("ACCTNAME") Into HaveMatch = Any() _
            Where Not HaveMatch _
            Select ACCTID = FreshData("ACCTID"), ACCTNAME = FreshData("ACCTNAME"), ACCTADDRESS = FreshData("ACCTADDRESS")

question is that how to give multiple constraints column in ON condition.
  I hope you got the point what  i want.

ok, that is possible to a point.  As I said above, you have to specify *explicitly* what the variable names will be so you can not have:
Select ACCTID = FreshData("ACCTID"), ACCTNAME = FreshData("ACCTNAME"), ACCTADDRESS = FreshData("ACCTADDRESS")

It stinks, but linq doesn't work that way, instead you will just get the whole row and have to work with that which really shouldt a problem, as you can just then use your strArrConstraints array values to get the values you need.  Then you just need to change the method a little bit (now it takes in an array)
    Function getNewUnique(ByVal dtFreshData As DataTable, ByVal dtSnapShot As DataTable, ByVal strFilter As String) As IEnumerable(Of DataRow)
        Dim query = From FreshData In dtFreshData.AsEnumerable() _
                    Group Join SnapShotData In dtSnapShot.AsEnumerable() _
                    On FreshData(strFilter(0)) Equals SnapShotData(strFilter(0)) _
                    And FreshData(strFilter(1)) Equals SnapShotData(strFilter(1)) _
                    Into HaveMatch = Any() Where Not HaveMatch _
                    Select FreshData
        Return query
    End Function

Open in new window

Author

Commented:
I think  you are able understand my question.
you are again writing a static query. i told every time strArrconstraint  can have any no of columns.
you have to write query based on all these columns.

Author

Commented:
I think  you are not able to understand my question.
you are again writing a static query. i told every time strArrconstraint  can have any no of columns.
you have to write query based on all these columns.
I can do something like this in C#, but VB has no ternary operators so I am not sure how to do this.

The problem is that you can not pull a loop in the middle of a linq query to make it truly dynamic.  What I do in C# is to write multiple lines and use a ternary operator to decide if it's used (sort of like an inline if/then statement that linq does allow).

So if this can be done in VB I don't know how.  But keep in mind once you are in a linq query, you can not run a loop.   Making linq dynamic is not all that easy.

Author

Commented:
ok. so how will you make this query in c# so that it can give correct results.

Author

Commented:
naspinski

I am waiting for your reply. i'll manage if your solution is in C#.
But i need it desperately.

Thanks
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.