带JOIN的实体数据源

本文关键字：数据源 实体 JOIN | 更新日期: 2023-09-27 18:15:50

我将GridView绑定到以下EntityDataSource:

<asp:EntityDataSource ID="sessionQuery" runat="server" 
    ConnectionString="name=encoreEntities" DefaultContainerName="encoreEntities"
    EnableFlattening="False" EntitySetName="sessions" Include="user, user.person" 
    Where="it.lastactivity > @NowMinusXMins" OrderBy="it.lastactivity desc" >   
    <WhereParameters>
        <asp:Parameter Name="NowMinusXMins" Type="DateTime" />
    </WhereParameters>
</asp:EntityDataSource>

我有另一个名为SessionHistory的表，它有一个进入Session的外键。该表跟踪会话期间访问的页面。我想把Session实体链接到SessionHistory中最近的记录，并在GridView中显示页面。

连接应该是这样的:

select * from session sWhere s.sessionid = (select top 1 sessionid from sessionhistory order by lastactivitydate desc)

我可以在这篇文章中这样做:EntityDataSource查询内部连接然而，我试图保留EF关系，以便我可以在我的Gridview中引用它们(例如。session.user。用户名、session.user.person。姓等)

有办法做到这一点吗?

带JOIN的实体数据源

这里有一个真实的例子，其中有两个表， 有三个导航属性。

<

表/strong>

  • lm_m_location_type table定义了一个位置(国家、城市、州等)
  • lm_m_location表包含按父/子层次结构排列的位置行(例如:USA -> Florida -> Tampa, Orlando, Miami)

  • lm_m_location_type定义了0到多位置
  • lm_m_location可以有0到多个子位置
  • lm_m_location可以有零或一个父位置

导航属性(从EntityFramework通过Edit XML)

  • NavigationProperty Name=ChildLocations Relationship=DB。fk_child_loc FromRole=lm_m_location ToRole=lm_m_location1

  • NavigationProperty Name=ParentLocation Relationship=DB。fk_parent_loc FromRole=lm_m_location1 ToRole=lm_m_location

  • NavigationProperty Name=LocationType Relationship=DB。fk_loc_type FromRole=lm_m_location ToRole=lm_m_location_type

代码示例:用于访问一个位置，包括父行、子行和位置类型行。

//
// code from the controller
//
public ActionResult getLocation()
{
    var lm_m_location = db.lm_m_location
        .Include("ParentLocation")
        .Include("ChildLocations")
        .Include("LocationType")
        .Where(t => t.parent_location_id == LinkDB.MvcApplication.Location.Current.id);
    return View(lm_m_location.ToList());
}
//
// code from the view that accesses each of the included tables
//
@model IEnumerable<LinkDBuilder.Models.lm_m_location>
@Html.DisplayFor(modelItem => item.description)
@Html.DisplayFor(modelItem => item.ParentLocation.description)
@Html.DisplayFor(modelItem => item.LocationType.description)
    // inside a loop of the child locations
    @Html.DisplayFor(modelItem => item.ChildLocations.description)
    // just to show we could drill back to the grand parent location if we wanted
    // of course we would make sure there was a value first in production code
    @Html.DisplayFor(modelItem => item.ParentLocation.ParentLocation.description)

.

相关文章：