sql—如何以及在何处添加两个实体表的更特定联接

我正在努力为我们公司建立一个我认为简单的目录。我不是一个web开发人员，我是一个独立的应用程序开发人员，所以我对asp.NETMVC和EntityFramework的知识有限。
我按照我在网上找到的一些说明，在一个旧的应用程序中创建了实体（2个表），用于获取雇员列表的sql语句使用了一个左外连接和3个where参数。我假设我必须在 model.Context.vb 文件？
我也看到了 Details 方法，但是它创建了这个上下文文件的一个示例，所以我假设在这里我需要合并这两个实体，然后通过控制器访问优化的列表，对吗？如果这是我需要做的，我将如何以及在哪里写linq语句？

这是控制器：

Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Entity
Imports System.Linq
Imports System.Net
Imports System.Web
Imports System.Web.Mvc
Imports MillDirectory

Namespace Controllers
Public Class tblUsersController
    Inherits System.Web.Mvc.Controller

    Private db As New HDDataEntities

    ' GET: tblUsers
    Function Index() As ActionResult
        Return View(db.tblUsers.ToList())
    End Function

    ' GET: tblUsers/Details/5
    Function Details(ByVal id As String) As ActionResult
        If IsNothing(id) Then
            Return New HttpStatusCodeResult(HttpStatusCode.BadRequest)
        End If
        Dim tblUser As tblUser = db.tblUsers.Find(id)
        If IsNothing(tblUser) Then
            Return HttpNotFound()
        End If
        Return View(tblUser)
    End Function

    ' GET: tblUsers/Create
    Function Create() As ActionResult
        Return View()
    End Function

    ' POST: tblUsers/Create
    'To protect from overposting attacks, please enable the specific properties you want to bind to, for 
    'more details see https://go.microsoft.com/fwlink/?LinkId=317598.
    <HttpPost()>
    <ValidateAntiForgeryToken()>
    Function Create(<Bind(Include:="USLanID,USAS400ID,USVaxID,USLName,USFname,USDepartment,USPhoneNumber,USFaxNumber,USExtension,USPagerNumber,USSupervisor,USPhoneAreaCode,USPagerAreaCode,USFaxAreaCode,USHelpDeskFlag,USAvailable,USAvailableDate,USEmail,USDomain,USInactive,KICKUser,KICKMinutes,USNewTicketNotify,USNAME,USClosedTicketnotify,USCellAreaCode,USCellPhoneNumber,USAllowedToPage,USLocation,USHomeArea,USHomePhone,USExcludeHomePhone,USEmpNum,USCellTxtMsgAddress,USHireDate,USTrackVac,USIncludeinCellDownload,USRecoveryMsg,USPowerMsg,USPMBreakMsg,USProdRPT,USStockRPT,USPagingType,USPicturePath,USTest,USPMBreakMins,USPMBreakNum,USmsgPCName")> ByVal tblUser As tblUser) As ActionResult
        If ModelState.IsValid Then
            db.tblUsers.Add(tblUser)
            db.SaveChanges()
            Return RedirectToAction("Index")
        End If
        Return View(tblUser)
    End Function

    ' GET: tblUsers/Edit/5
    Function Edit(ByVal id As String) As ActionResult
        If IsNothing(id) Then
            Return New HttpStatusCodeResult(HttpStatusCode.BadRequest)
        End If
        Dim tblUser As tblUser = db.tblUsers.Find(id)
        If IsNothing(tblUser) Then
            Return HttpNotFound()
        End If
        Return View(tblUser)
    End Function

    ' POST: tblUsers/Edit/5
    'To protect from overposting attacks, please enable the specific properties you want to bind to, for 
    'more details see https://go.microsoft.com/fwlink/?LinkId=317598.
    <HttpPost()>
    <ValidateAntiForgeryToken()>
    Function Edit(<Bind(Include:="USLanID,USAS400ID,USVaxID,USLName,USFname,USDepartment,USPhoneNumber,USFaxNumber,USExtension,USPagerNumber,USSupervisor,USPhoneAreaCode,USPagerAreaCode,USFaxAreaCode,USHelpDeskFlag,USAvailable,USAvailableDate,USEmail,USDomain,USInactive,KICKUser,KICKMinutes,USNewTicketNotify,USNAME,USClosedTicketnotify,USCellAreaCode,USCellPhoneNumber,USAllowedToPage,USLocation,USHomeArea,USHomePhone,USExcludeHomePhone,USEmpNum,USCellTxtMsgAddress,USHireDate,USTrackVac,USIncludeinCellDownload,USRecoveryMsg,USPowerMsg,USPMBreakMsg,USProdRPT,USStockRPT,USPagingType,USPicturePath,USTest,USPMBreakMins,USPMBreakNum,USmsgPCName")> ByVal tblUser As tblUser) As ActionResult
        If ModelState.IsValid Then
            db.Entry(tblUser).State = EntityState.Modified
            db.SaveChanges()
            Return RedirectToAction("Index")
        End If
        Return View(tblUser)
    End Function

    ' GET: tblUsers/Delete/5
    Function Delete(ByVal id As String) As ActionResult
        If IsNothing(id) Then
            Return New HttpStatusCodeResult(HttpStatusCode.BadRequest)
        End If
        Dim tblUser As tblUser = db.tblUsers.Find(id)
        If IsNothing(tblUser) Then
            Return HttpNotFound()
        End If
        Return View(tblUser)
    End Function

    ' POST: tblUsers/Delete/5
    <HttpPost()>
    <ActionName("Delete")>
    <ValidateAntiForgeryToken()>
    Function DeleteConfirmed(ByVal id As String) As ActionResult
        Dim tblUser As tblUser = db.tblUsers.Find(id)
        db.tblUsers.Remove(tblUser)
        db.SaveChanges()
        Return RedirectToAction("Index")
    End Function

    Protected Overrides Sub Dispose(ByVal disposing As Boolean)
        If (disposing) Then
            db.Dispose()
        End If
        MyBase.Dispose(disposing)
    End Sub
End Class
End Namespace

方法1：您可以通过在控制器中添加这个来提取结果集。我已编辑索引操作：

Function Index() As ActionResult
    var query = from u in db.tblUser
                join p in db.tblUserPermission on u.UserID equals p.UserID
                where u.YourProperty == YourCondition 
                into gj
                from x in gj.DefaultIfEmpty()
                select new { 
                 UserID = u.UsergroupID,
                 UserPermission = p.UserPermission               
                };
    Return View(query)
End Function

方法2：

Function Index() As ActionResult
    Return View(db.UserDbSet.UserPermissionList.Where(w => w.YourProperty == 
                w.YourFirstCond && w.YourAnotherProp == w.YourSecondCond).ToList())        
End Function

假设左表的dbset是userdbset。在此之前，您需要根据您的关系更改模型（实体）。我已经给出了一对多关系的场景。

//In tblUser Model
public List<tblUserPermission> UserPermissionList {get;set;}

//In tblUserPermission model
public tblUser User {get;set;}

抱歉，模型和第一种方法是c代码。

sql—如何以及在何处添加两个实体表的更特定联接

1条答案

相关问题

热门标签

最新问答