specific VB Linq Query help

Question

I am still new to LINQ, and I have wrestled with a query for several days and am ready to surrender to ignorance on this one.

I need to: join 3 tables (on a total of 2 databases). Lets call them Table1 Table2 Table3

Table1 joins to Table2 on "org"

the result joins to Table3 on "emplid"

Filters (where):

Table3.success = true

Table3.appcode = "neo"

Table1.empl_rcd = 0

start_date (one that I can pass in as a variable)

end_date (one that I can pass in as a variable)

Groupings: (I think grouping is how you would do it?)

Form the joined-up table must only select the earliest (most distant past) "stamp" date field for each "emplid" grouping ("stamp" originally on Table3, "emplid" originally on Table3 and Table1)

From the joined-up table must only select the latest (most recent) "effdt" date field for each "emplid" grouping ("effdt" originally on Table1, "emplid" originally on Table3 and Table1)

Select:

I need to select all the returned rows from the previous logic. I can figure out how to filter the final rows I'll need.

Here is the last thing I got to execute, and it may give you an idea of what I was doing:

query = (From j In db.table1s _
Join a In db.table3s _
On j.EMPLID Equals a.emplid _
Join d In db.table2s _
On d.ORG Equals j.Org _
Where a.appcode = "neo" _
Where a.success = True _
Select a.appcode, j.effdt, a.stamp, j.EMPLID, _
d.ORGANIZATION_DESCRIPTION, d.DEPARTMENT_DESCRIPTION, d.VP_DESCRIPTION, _
a.success _
Distinct).AsQueryable().AsEnumerable()

If you can get me this far, I'll give you credit. But if you can do the next thing too, before I give out credit, I'll give YOU credit.

then out of that result I need to return a count of unique "emplid" for each "org". So the very end result would look like:

   org    |     count    |   (other columns)
------------------------------------------------
 12345    |     15       |       etc...
 54321    |     7        |       etc...

thanks for helping out a noob.

Answer 1

It's not exactly clear how your various EmplId grouping requirements relate to counting employees, but this should get you started:

query = From j In db.table1s _
        Join a In db.table3s On j.EMPLID Equals a.emplid _
        Join d In db.table2s On d.ORG Equals j.Org _
        Where a.appcode = "neo" _
        Where a.success = True _
        Group By j.Org, d.ORGANIZATION_DESCRIPTION Into Count()

VB Group By on MSDN

Answer 2

I ended up using this query which may not be the most efficient, but it works. My biggest problem was I didn't know how to do sub queries properly.

query = From final In _
        (From subfinal In _
            (From countdata In _
                (From a In _
                    (From aa In db.table3s _
                    Where aa.appcode = "neo" _
                    Where aa.success = True _
                    Group By aa.emplid Into Group _
                    Select emplid = Group.Min(Function(f) f.emplid), _
                           stampMin = Group.Min(Function(o) o.stamp) _
                    ) _
                Join j In _
                    (From jj In db.table1s _
                    Where jj.empl_rcd = 0 _
                    Group By jj.EMPLID Into Group _
                    Select EMPLID = Group.Max(Function(m) m.EMPLID), _
                        EffDate = Group.Max(Function(z) z.effdt) _
                    ) _
                On j.EMPLID Equals a.emplid _
                Join jorg In _
                    (From jj2 In db.table1s _
                    Where jj2.empl_rcd = 0 _
                    Select jj2.EMPLID, jj2.Org, jj2.effdt _
                    ) _
                On j.EMPLID Equals jorg.EMPLID _
                And j.EffDate Equals jorg.effdt _
                Select j.EMPLID, a.stampMin, j.EffDate, jorg.Org _
                Distinct) _
            Where countdata.stampMin < EndDate _
            And countdata.stampMin > StartDate _
            Group By countdata.Org Into Group _
            Select count = Group.Count, Org = Org _
            ) _
        Select subfinal.Org, subfinal.count _
        ) _
        Join d In _
            (From dd In db.table2s _
            Select dd.ORG, dd.ORGANIZATION_DESCRIPTION, dd.DEPARTMENT_DESCRIPTION, _
                    dd.VP_COLLEGE_DESCRIPTION _
            ) _
        On final.Org Equals d.ORG _
    Select final.count, final.Org, Org_desc = d.ORGANIZATION_DESCRIPTION, _
    Dept_descr = d.DEPARTMENT_DESCRIPTION, Coll_descr = d.VP_COLLEGE_DESCRIPTION

Hope this helps someone else...