Link to home
Start Free TrialLog in
Avatar of TSmooth
TSmooth

asked on

LINQ 2 SQL Query Syntax

I have several tables that center around an organization table that simply holds a unique ID value. Each Organization can then be at a particular location and have a particular name. The tricky part is that the organizations support location and name changes with a specified effective date of each change. For this example I have 4 relevant tables:

Organization: ID (PK, int, identity)
Location: ID (PK, int, identity), Name (varchar), AltLat (float), AltLong (float)
organization_locations: organization_id(FK, int), location (FK, int), eff_date (datetime)
organization_names: organization_id (FK, int), name (ntext), eff_date (datetime), icon (nvarchar(100))

What I need to retrieve is the list of all locations along with all organizations at a given location as of a specific date and project them into my business entities. In other words, I will have a date provided and need to return for each location, the organization related to the organization_location entry with the most recent eff_date that is less than the date provided. Same thing goes for each organization, I'd need the name as of the date.

What I started with can be found in the code below. It doesn't seem to give me the correct organization name.

I'd prefer VB syntax but if you can only give me a C# query I can work with that. I've tried a few other variations but I end up getting syntax errors about an expected "}" or members not being a part of an entity set no matter what combination of parenthesis I try.
Dim query = From loc In dc.Locations _
               Where loc.AltLong IsNot Nothing And loc.AltLat IsNot Nothing _
               Select New AnnexA.Entities.AnnexALocation With {.ID = loc.ID, .Name = loc.Location, .Y = loc.AltLat, .X = loc.AltLong, _
                                .Units = From ol In loc.organization_locations Let o = ol.Organization.organization_names.Where(Function(ed) (ol.eff_date < Date.Parse("1/1/2011"))).OrderByDescending(Function(od) (od.eff_date)).First() Select New AnnexA.Entities.AnnexAMillitaryUnit With {.ID = o.ID, .Name = o.name, .IconPath = o.icon}}

Open in new window

Avatar of Fernando Soto
Fernando Soto
Flag of United States of America image

If you have test db of the four tables with some data in it I will see what I can come up with.
Avatar of TSmooth
TSmooth

ASKER

I've attached a sample mdf. If you query with a date between 1/1/2000 and 1/1/2008, you should get back 3 locations with 2 orgs each except for the last location which should only have one org. If you query with a date between 1/1/2008 and 1/1/2010, you should see that Org 1 changed from Location A to Location B. If you query anytime post 1/1/2010, Org 6 should show up at Location C and Org 4 should then be named "Org 4 Renamed".  If you were to query anytime prior to 1/1/2000, you should get a collection of the 3 locations but none of the locations should have any orgs. Rename the file inside the zip to .MDF since Experts Exchange won't allow the extension.
EETestDB.zip
SOLUTION
Avatar of Fernando Soto
Fernando Soto
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of TSmooth

ASKER

I think it's going in the right direction but you might be misunderstanding the requirement. The date filter should only take one date. I mentioned the range to give you an idea of what the results should be if you picked one date within that range based on the sample data provided. The arbitrary range I gave will not be part of the filter. I need to know what location an organization was at on a specific date as well as what it was called on that date. The data specifies the effective dates as the date of change.

Your query above places "Org 1" at both Location A and Location B when queried using the date 1/1/2020 when really it should just be at Location B. It also seems to place some blank organizations at locations in cases where the organization will be at the location at a future date.
Avatar of TSmooth

ASKER

Just to maybe help give a better understanding of what I need. This is a sample sql stored procedure that I  made a while ago which produces the results similar to what I'm looking to do with linq.


CREATE PROCEDURE [dbo].[GetAnnexATableTEST]
	@asof_date datetime
AS
BEGIN
	SELECT * FROM (
		SELECT O.ID, 
			names.name, names.eff_date AS eff_date_name, 
			locs.location, locs.name As loc_name, locs.eff_date AS eff_date_Location
	FROM AnnexA.Organizations O
			LEFT OUTER JOIN
			(SELECT ID, organization_id, name, eff_date FROM (
			SELECT ID, organization_id, name, eff_date, row_number() over(partition by organization_id order by eff_date desc) AS rn FROM AnnexA.organization_names WHERE eff_date<@asof_date) t
				WHERE rn=1) names ON O.ID = names.organization_id
			LEFT OUTER JOIN
				(SELECT t.ID, organization_id, t.location, l.location As name, eff_date FROM(
				SELECT ID, organization_id, location, eff_date, row_number() over (partition by organization_id order by eff_date desc) AS rn FROM AnnexA.organization_locations WHERE eff_date<@asof_date) t
				INNER JOIN AnnexA.Locations l ON t.location = l.id
				WHERE rn=1) locs ON O.ID = locs.organization_id
		) G
END

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of TSmooth

ASKER

Using your most recent query, If you use the date "1/10/2008", you should see that Org 1 is showing up at both location A and Location B. It should only show up at Location B at that point. Also the name of "Org 4" is always "Org 4 Renamed" when it shouldn't be that until we query a date after 1/1/2010. I think that's just because this initial stab at the query is ignoring that part for now.
I have abandon your solution and just join the tables and filtered on the date.
Dim dc As New LocationDataContext()
 
Dim tblLocation As Table(Of Location) = dc.GetTable(Of Location)()
Dim tblOrgLocation As Table(Of organization_location) = _
    dc.GetTable(Of organization_location)()
Dim tblOrganization As Table(Of Organization) = dc.GetTable(Of Organization)()
Dim tblOrgName As Table(Of organization_name) = dc.GetTable(Of organization_name)()
 
Dim orgData = From loc In tblLocation _
              Join orgLoc In tblOrgLocation On loc.ID Equals orgLoc.location_id _
              Join org In tblOrganization On orgLoc.organization_id Equals org.ID _
              Join orgName In tblOrgName On org.ID Equals orgName.organization_id _
              Where orgLoc.eff_date = Date.Parse(SearchData.Text.Trim()) _
              Order By loc.ID, orgLoc.eff_date Descending _
              Select loc, orgLoc, org, orgName
 
For Each od In orgData
    Console.WriteLine("Loc ID = {0}" & vbTab & "Name = {1}" & vbTab & "AltLat = {2}" & vbTab & "AltLong = {3}", _
                      od.loc.ID, od.loc.Name, od.loc.AltLat, od.loc.AltLong)
    Console.WriteLine(vbTab & "Org ID = {0}" & vbTab & "Name = {1}" & vbTab & "IPath = {2}", _
                      od.orgName.ID, od.orgName.Name, od.orgName.Icon)
Next

Open in new window

Avatar of TSmooth

ASKER

I have managed to get the first part of it working, getting the organizations to show up at their proper locations. The working code is the first snippet listed below. The second snippet is my attempt to get the name piece working doing a similar thing but I can't seem to get the syntax right. Any ideas on how to fix the syntax of the "Let" piece in the second snippet?

Dim ssDate As Date
        ssDate = Date.Parse("1/10/2000")
 
        Dim query3 = From loc In dc.Locations _
                     Where loc.AltLat IsNot Nothing And loc.AltLong IsNot Nothing _
                     Select New With {.ID = loc.ID, .Name = loc.Name, .Y = loc.AltLat, .X = loc.AltLong, _
                                     .Units = From mu In (From ol In dc.organization_locations _
                                                         Where ol.eff_date < ssDate _
                                                         Order By ol.eff_date Descending _
                                                         Group ol By ol.organization_id Into g = Group _
                                                         Select New With { _
                                                            Key .Key = organization_id, _
                                                            .Result = g.Take(1)}).SelectMany(Function(a) (a.Result)) _
                                                            .Where(Function(b) (b.location_id = loc.ID)) _
                                                            Select New AnnexAMillitaryUnit With {.ID = mu.Organization.ID, _
                                                                                                 .Name = "test", _
                                                                                                 .IconPath = "test"}}
 
' Second query attempt to add in the name support which is not working
        Dim ssDate As Date
        ssDate = Date.Parse("1/10/2000")
 
        Dim query3 = From loc In dc.Locations _
                     Where loc.AltLat IsNot Nothing And loc.AltLong IsNot Nothing _
                     Select New With {.ID = loc.ID, .Name = loc.Name, .Y = loc.AltLat, .X = loc.AltLong, _
                                     .Units = From mu In (From ol In dc.organization_locations _
                                                         Where ol.eff_date < ssDate _
                                                         Order By ol.eff_date Descending _
                                                         Group ol By ol.organization_id Into g = Group _
                                                         Select New With { _
                                                            Key .Key = organization_id, _
                                                            .Result = g.Take(1)}).SelectMany(Function(a) (a.Result)) _
                                                            .Where(Function(b) (b.location_id = loc.ID)) _
                                                            Let name = (From orgName In dc.organization_names _
                                                                       Where orgName.eff_date < ssDate _
                                                                       Order By orgName.eff_date Descending _
                                                                       Group orgName By orgName.organization_id Into gName = Group _
                                                                       Select New With { _
                                                                        Key .Key = organization_id, _
                                                                        .Result = gName.Take(1)}).SelectMany(Function(a) (a.Result)) _
                                                                        .Where(Function(b) (b.organization_id = mu.organization_id)) _
                                                                        .SelectMany(Function(c) (c.Result)) _
                                                            Select New AnnexAMillitaryUnit With {.ID = mu.Organization.ID, _
                                                                                                 .Name = name.Name, _
                                                                                                 .IconPath = name.Icon}}

Open in new window

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Your quicker then I am, I just started working on it.  ;=)