troubleshooting Question

LINQ 2 SQL Query Syntax

Avatar of TSmooth
TSmooth asked on
.NET ProgrammingC#Visual Basic.NET
11 Comments1 Solution1327 ViewsLast Modified:
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}}
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 11 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros