LINQ to SQL Query Syntax
I have multiple tables that are located around an organization table that simply contains a unique ID value. Each Organization can be in a certain place and have a certain name. The tricky part is that organizations support location and name changes with a specified effective date for each change. For this example, I have 4 relevant tables:
Organization: identifier (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))
I need to get a list of all locations along with all organizations in a specific location on a specific date and project them into my business entities. In other words, I will have dates specified and you will need to return for each location, the organization associated with the organization_location record with the most recent eff_date that is less than the specified date. The same goes for every organization, I need a name from the date.
This is what I started with this, but it doesn't work:
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}}
I would prefer the VB syntax, but if you can only give me a C # query, I can work with that. I've tried several other options, but I end up getting syntax errors regarding the expected "}" or members that are not part of the set of objects, no matter what combination of parentheses I try.
source to share
I ended up using the following code:
Dim query4 = From loc In dc.Locations _
Let curNames = (From ons In dc.organization_names _
Where ons.eff_date <= ssDate _
Order By ons.eff_date Descending _
Group ons By ons.organization_id Into gNames = Group _
Select New With { _
Key .Key = organization_id, _
.Result = gNames.Take(1)}) _
.SelectMany(Function(a) (a.Result)) _
Let curLocs = (From oLocs In dc.organization_locations _
Where oLocs.eff_date <= ssDate _
Order By oLocs.eff_date Descending _
Group oLocs By oLocs.organization_id Into gLocs = Group _
Select New With { _
Key .Key = organization_id, _
.Result = gLocs.Take(1)}) _
.SelectMany(Function(a) (a.Result)) _
Where loc.AltLat IsNot Nothing And loc.AltLong IsNot Nothing _
Select New AnnexA.Entities.AnnexALocation With { _
.ID = loc.ID, .Name = loc.Location, .Y = loc.AltLat, .X = loc.AltLong, _
.Units = From curLoc In curLocs _
Where curLoc.location = loc.ID _
From curName In curNames _
Where curName.organization_id = curLoc.organization_id _
Select New AnnexA.Entities.AnnexAMillitaryUnit With { _
.ID = curLoc.organization_id, _
.Name = curName.name, _
.IconPath = curName.icon}}
Thanks for taking the time to look at this!
source to share
I think I understand what you are trying to do here, and it looks like some simple joins might get rid of all the complicated stuff you do at the end of your query. (It's in C #, but it should be pretty straight forward to get it in VB)
from loc in dc.Locations
join ol in dc.organization_locations on loc.ID equals ol.location
join orn in dc.organization_names on ol.organization_id equals orn.organization_id
where loc.AltLong != null
&& loc.AltLong != null
&& ol.eff_date < Date.Parse("1/1/2011")
orderby ol.eff_date
select new AnnexA.Entities.AnnexAMillitaryUnit
{ ID = loc.ID, Name = orn.name, IconPath = orn.icon}
Let me know if this works, or if it needs tweaking ...
source to share
I need the most recent (for now) place for the organization and the name of the organization taken into account. Not just everything is up to date. For example, take the President of the United States, President-elect Obama. In this example, there are 3 locations: his house, the Hay-Adams hotel, and the White House. Over the past couple of months, he also stepped down to 3 titles: Senator Obama, President Obama-elected, and will soon become President Obama.
If you were to pass the date "1/1/2008" to this request, you must return all 3 locations, and "Senator Obama" (organization name) goes to his "home" (organization_location) and the other two locations must not "organizations" within them using our sample. If you were to attend the 12/1/2008 date, he was still technically living at home, but then he got the title of President Obama elected, so the results would be reflected in that. If you walked in today, his name will still be President Obama's Chosen, but his location has changed to Hay-Adams Hotel. If you pass on any date after "1/20/2009", his title will be "President Obama" and his location will be "The Whitehouse".
I hope this made some sense, even if you have no interest in politics or not in the US. I need the results to show me where everything was and what everything was called at a given time.
source to share