代码之家 › 专栏 › 技术社区 › Tom

LINQ到SQL查询语法

linq-to-sql linq vb.net

Tom · 技术社区 · 17 年前

组织:ID(主键、整数、标识)

位置:ID(主键、int、标识)、Name(varchar)、AltLat(浮点)、AltLong(浮点)

组织位置:组织id(FK,int)、位置(FK,int)、生效日期(datetime)

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}}

3 回复 | 直到 17 年前

Noah 17 年前

我想我理解您在这里试图做什么,而且看起来一些简单的连接可以消除您在查询结束时所做的所有复杂工作。(它是C语言的,但是它应该非常直接地将它放到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}

让我知道这是否有效或是否需要任何微调。。。

Tom 17 年前

如果您要将日期“1/1/2008”传递到此查询中,您应该返回所有3个位置,其中“参议员奥巴马”(组织名称)位于他的“房子”(组织位置)内,并且使用我们的示例,其他两个位置中不应有“组织”。如果你将通过“12/1/2008”的日期,从技术上讲,他仍然住在家里,但他当时拥有“当选总统奥巴马”的头衔,因此结果将反映这一点。如果你把今天的日期传过去,他的名字仍然是“当选总统奥巴马”,但他的位置改为“海亚当斯酒店”。如果你在“1/20/2009”之后输入任何日期,他的头衔将是“奥巴马总统”,他的位置将是“白宫”。

我希望这是有道理的,即使你对政治不感兴趣或者不是美国人。我需要结果来告诉我在某个特定的时间点,一切都在哪里,一切都被称为什么。

Tom 17 年前

我最终通过以下代码实现了这一点:

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}}