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

xqkwcwgp  于 2021-08-13  发布在  Java
关注(0)|答案(1)|浏览(395)

我正在努力为我们公司建立一个我认为简单的目录。我不是一个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
jvidinwx

jvidinwx1#

方法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代码。

相关问题