Avatar of sudgupta
sudgupta
 asked on

Build dynamic LINQ Query over Datatables

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

.NET ProgrammingVisual Basic.NET

Avatar of undefined
Last Comment
naspinski

8/22/2022 - Mon
naspinski

what is HaveMatch?
naspinski


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

sudgupta

ASKER
naspinski
I  am a beginner on linq. please tell me how to give constraints in strFilter
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
naspinski

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

sudgupta

ASKER
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
naspinski

What exactly do you mean by more constraints?  Where will they be?  The dynamic ability of linq is limited in some areas.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
sudgupta

ASKER
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.

naspinski

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

sudgupta

ASKER
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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
sudgupta

ASKER
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.
naspinski

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.
sudgupta

ASKER
ok. so how will you make this query in c# so that it can give correct results.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
sudgupta

ASKER
naspinski

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

Thanks
ASKER CERTIFIED SOLUTION
naspinski

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.