使用LinQ来访问多个表,用LinQ连接这些表的正确方法是什么，以使其与SQL查询相同？

jvlzgdj9  于 2023-02-17  发布在  其他
关注(0)|答案(1)|浏览(166)

我试图访问数据仓库中的所有表，这些表是我使用Lin-Q为一个实验项目创建的，我似乎不明白如何访问与数据结构有关的每个表，但可以使用SQL Server轻松地查询以获得结果。我可以访问DimLocation表，但不能访问其他表。我认为在控制器中是正确的方法，但不知道如何使用Lin-Q选择每个表的每一列。任何帮助将不胜感激！
我的程序：GlobalCommandCenter
SQL查询结果连接表：

要从实体框架连接的表：

LinQ I创建用于连接表（需要帮助将所有表连接为SQL）：

我可以使用一个表，它工作得很好，但是当我尝试像Michael描述的那样处理多个表时，我得到了这个错误。

在控制器中添加了多个连接代码：

LocationDatas = (List<DimLocation>)(from c in entities.DimLocations
                         join f in entities.FactInventories
                         on c.LocationId equals f.LocationId
                         join p in entities.DimProductInventories
                         on f.ProductId equals p.ProductId
                         join dd in entities.DimDates
                         on f.DateSK equals dd.DateSK
                         join dt in entities.DimTimes
                         on f.TimeSK equals dt.TimeSK
                         where c.ContinentName == continent && c.CountryName == country 
                         && c.CountryName == c.CountryName && c.ContinentName == c.ContinentName 
                         && c.ContinentName == continent || string.IsNullOrEmpty(continent) || string.IsNullOrEmpty(country) 
                         && c.ContinentName == continent
                         select new 
                         {
                             c.LocationId,
                             c.ContinentName,
                             c.CountryName,
                             c.RegionName,
                             c.CityName,
                             p.ProductName,
                             f.ProductPrice,
                             f.ProductQty,
                             dd.StandardDate,
                             dt.StandardTime
                         }),

添加多个联接代码后出现错误：

我编辑了对模型的调用，去掉了方括号，并在它的最后添加了一个分号。然后，我向每个输出添加了var，并在每个语句的最后添加了一个分号。之后，我在视图上遇到了错误。

视图上的编译错误（Razor视图中是否存在IEnumerable兼容错误？）：

public static CommandCenterModel PopulateModel(string continent, string country)
{
    using (GlobalCommandCenter3Entities entities = new GlobalCommandCenter3Entities())
    {
        **CommandCenterModel model = new CommandCenterModel()
        {**
            //Join all the tables together and extract the information into one web grid. 
            LocationDatas = (from c in entities.DimLocations
                             join f in entities.FactInventories
                             on c.LocationId equals f.LocationId
                            
                             join p in entities.DimProductInventories
                             on f.ProductId equals p.ProductId
                             
                             join dd in entities.DimDates
                             on f.DateSK equals dd.DateSK

                             join dt in entities.DimTimes
                             on f.TimeSK equals dt.TimeSK
                             where c.ContinentName == continent && c.CountryName == country && c.CountryName == c.CountryName 
                             && c.ContinentName == c.ContinentName && c.ContinentName == continent || string.IsNullOrEmpty(continent) 
                             || string.IsNullOrEmpty(country) && c.ContinentName == continent
                             select  c).ToList(),    

            Country = (from c in entities.DimLocations
                       orderby c.CountryName
                       where !string.IsNullOrEmpty(c.CountryName) && c.CountryName != null && continent == c.ContinentName
                       select new SelectListItem { Text = c.CountryName, Value = c.CountryName }).Distinct().ToList(),
 

            Continent = (from c in entities.DimLocations
                                 orderby c.ContinentName
                                 where (c.ContinentName == c.ContinentName)
                                 select new SelectListItem { Text = c.ContinentName, Value = c.ContinentName }).Distinct().ToList(),
            /*
            Location = (from c in entities.DimLocations
                             join f in entities.FactInventories
                             on c.LocationId equals f.LocationId
                             join p in entities.DimProductInventories
                             on f.ProductId equals p.ProductId
                             join dd in entities.DimDates
                             on f.DateSK equals dd.DateSK
                             join dt in entities.DimTimes
                             on f.TimeSK equals dt.TimeSK
                             where c.ContinentName == continent && c.CountryName == country && c.CountryName == c.CountryName && c.ContinentName == c.ContinentName && c.ContinentName == continent || string.IsNullOrEmpty(continent) || string.IsNullOrEmpty(country) && c.ContinentName == continent
                             select new { c.ContinentName, c.CountryName, c.RegionName, c.CityName, f.ProductPrice, f.ProductQty, dd.StandardDate, dt.StandardTime }).ToList(),
            */

        };
        //Allow to stay on selection. 
        model.SectionCity = continent;
        return model;
    }
}

SQL查询：

SELECT ContinentName, CountryName, RegionName, CityName, ProductName, 
    finv.ProductPrice, 
    finv.ProductQty, StandardDate, StandardTime 
    FROM DimLocation dloc
    INNER JOIN FactInventory finv ON finv.LocationId = dloc.LocationId
    INNER JOIN DimProductInventory dprod ON finv.ProductId = dprod.ProductId
    INNER JOIN DimDate ddat ON finv.DateSK = ddat.DateSK
    INNER JOIN DimTime dtim ON finv.TimeSK = dtim.TimeSK

                SELECT * FROM FactInventory   
                SELECT * FROM DimLocation
                SELECT * FROM DimDate            
                SELECT * FROM DimTime
                SELECT * FROM DimProductInventory

控制器：

using ClassLibraryDAL.DAL;
    using OperationsCommandCenter.Models;
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.Mvc;

    namespace OperationsCommandCenter.Controllers
   {
    public class HomeController : Controller
   {
    // GET: Home
    public ActionResult Index()
    {
        CommandCenterModel model = PopulateModel(null, null);

        return View(model);
    }

    //Controls DropDownList.
    [HttpPost]
    public ActionResult Index(string continent, string country)
    {
        CommandCenterModel model = PopulateModel(continent, country);

        return View(model);
    }

    //Population Controller. References list selection.
    public static CommandCenterModel PopulateModel(string continent, string country)
    {
        using (GlobalCommandCenter3Entities entities = new 
        GlobalCommandCenter3Entities())
        {
            CommandCenterModel model = new CommandCenterModel()
            {
                //Join all the tables together and extract the information into one web 
                  grid. 
                LocationDatas = (from c in entities.DimLocations
                                 join f in entities.FactInventories
                                 on c.LocationId equals f.LocationId
                                 
                                 join p in entities.DimProductInventories
                                 on f.ProductId equals p.ProductId
                                 
                                 join dd in entities.DimDates
                                 on f.DateSK equals dd.DateSK

                                 join dt in entities.DimTimes
                                 on f.TimeSK equals dt.TimeSK
                                 where c.ContinentName == continent && c.CountryName == 
        country && c.CountryName == c.CountryName && c.ContinentName == c.ContinentName 
        && c.ContinentName == continent || string.IsNullOrEmpty(continent) || 
        string.IsNullOrEmpty(country) && c.ContinentName == continent
                                 select  c).ToList(),  

                Country = (from c in entities.DimLocations
                           orderby c.CountryName
                           where !string.IsNullOrEmpty(c.CountryName) && c.CountryName 
         != null && continent == c.ContinentName
                           select new SelectListItem { Text = c.CountryName, Value = 
                           c.CountryName }).Distinct().ToList(),

                Continent = (from c in entities.DimLocations
                                     orderby c.ContinentName
                                     where (c.ContinentName == c.ContinentName)
                                     select new SelectListItem { Text = c.ContinentName, 
                                     Value = c.ContinentName }).Distinct().ToList(),
                /*
                Location = (from c in entities.DimLocations
                                 join f in entities.FactInventories
                                 on c.LocationId equals f.LocationId
                                 join p in entities.DimProductInventories
                                 on f.ProductId equals p.ProductId
                                 join dd in entities.DimDates
                                 on f.DateSK equals dd.DateSK
                                 join dt in entities.DimTimes
                                 on f.TimeSK equals dt.TimeSK
                                 where c.ContinentName == continent && c.CountryName == 
       country && c.CountryName == c.CountryName && c.ContinentName == c.ContinentName 
      && c.ContinentName == continent || string.IsNullOrEmpty(continent) || 
      string.IsNullOrEmpty(country) && c.ContinentName == continent
                                 select new { c.ContinentName, c.CountryName, 
       c.RegionName, c.CityName, f.ProductPrice, f.ProductQty, dd.StandardDate, 
       dt.StandardTime }).ToList(),
                */

            };
            //Allow to stay on selection. 
            model.SectionCity = continent;
            return model;
        }
    }
    }
    }

型号：

using ClassLibraryDAL.DAL;
   using System;
   using System.Collections.Generic;
   using System.Linq;
   using System.Web;
   using System.Web.Mvc;

   namespace OperationsCommandCenter.Models
   {
   public class CommandCenterModel
   {
    public List<DimLocation> LocationDatas { get; set; }
    public List<DimProductInventory> ProductInventory { get; set; }
    public List<SelectListItem> Country { get; set; } 
    public List<SelectListItem> Continent { get; set; } 

    public List<SelectListItem> ProductName { get; set; } 

    //public List<DimLocation> Location { get; set; }

    public string SectionCity { get; set; }

}
}

查看：

@model  OperationsCommandCenter.Models.CommandCenterModel

@{
ViewBag.Title = "Home Page";
string Country = Model.Country.ToString();
string SectionCity = Model.SectionCity;

 WebGrid webGrid = new WebGrid(source: Model.LocationDatas, canPage: true, canSort: 
   true, sortDirectionFieldName: "ContinentName, CountryName, RegionName, CityName", 
   rowsPerPage: 20);
webGrid.Pager(WebGridPagerModes.All);

}

<!DOCTYPE html>

<head>
<meta name="viewport" content="width=device-width" />

<title>Global Command Center</title>
<link href="@Url.Content("~/Content/YardDogStyle.css")" rel="stylesheet" type="text/css" 
/>
<link href="@Url.Content("~/Scripts/jquery-3.4.1.min.js")" rel="stylesheet" 
 type="text/css" />
<link href="@Url.Content("~/Scripts/bootstrap.min.js")" rel="stylesheet" type="text/css" 
 />

<script src="~/Scripts/bootstrap.min.js"></script>
<script src="~/Scripts/jquery-3.4.1.min.js"></script>
<script src="Scripts/jquery-1.4.1.min.js" type="text/javascript"></script>
<script src="~/Scripts/jquery-1.4.1.min.js"></script>

<link href="~/Content/YardDogStyle.css" rel="stylesheet" />

  </head>
  <body>
<div id="time"></div>
<div id="RowCount"></div>
<SCRIPT LANGUAGE="Javascript">

    function checkTime(i) {
        if (i < 10) {
            i = "0" + i;
        }
        return i;
    }
    function startTime() {
        var today = new Date();
        var h = today.getHours();
        var m = today.getMinutes();
        var s = today.getSeconds();

        // add a zero in front of numbers<10
        m = checkTime(m);
        s = checkTime(s);
        document.getElementById('time').innerHTML = h + ":" + m + ":" + s; //Get the 
        time.
        document.getElementById('time').innerHTML = "Date: " + today; //Get the Date.
        t = setTimeout(function () {
            startTime()
        }, 500);
    }
    startTime();
</SCRIPT>
<script src="Scripts/jquery-1.4.1.min.js" type="text/javascript"></script>
<script src="Scripts/ScrollableGridPlugin.js" type="text/javascript"></script>
<script type="text/javascript">
    $(document).ready(function () {
        $('#<%=webGrid.ClientID %>').Scrollable({
            ScrollHeight: 300
        });
        $('#<%=webGrid.ClientID %>').Scrollable({
            ScrollHeight: 300
        });
    });
</script>

<script src="Scripts/ScrollableGridPlugin.js" type="text/javascript"></script>
<script src="~/Scripts/ScrollableGridPlugin.js"></script>
<script type="text/javascript">
    $(document).ready(function () {
        $('#<%=webGrid.ClientID %>').Scrollable({
            ScrollHeight: 300,
            Width: 467
        });
    });
</script>
<script type="text/javascript">
    //Count the rows on the webGrid that are visible... is my intent with this code.
    function CountRows() {
        var totalRowCount = 0;
        var rowCount = 0;
        var gridView = document.getElementById("<%=webGrid.ClientID %>");
        var rows = gridView.getElementsByTagName("tr")
        for (var i = 0; i < rows.length; i++) {
            totalRowCount++;
            if (rows[i].getElementsByTagName("td").length > 0) {
                rowCount++;

            }

        }
        var message = "Total Row Count: " + totalRowCount;
        message += "\nRow Count: " + rowCount;
        //alert(message);
        message = document.getElementById('RowCount').innerHTML = message;

        // return false;

    }
    CountRows();
</script>
<form id="formYardDog" class="formYardDog" runat="server" method="post">

    @{ int firstRecord = (webGrid.PageIndex * webGrid.RowsPerPage) + 1;
        int lastRecord = (webGrid.PageIndex * webGrid.RowsPerPage) + webGrid.Rows.Count;

        // webGrid.Rows..GetRowCount(DataGridViewElementStates.Visible);
    }
    <div id="RowCountTop"><b>Records: @firstRecord - @lastRecord of 
    @webGrid.TotalRowCount</b></div><br />

    @using (Html.BeginForm("Index", "Home", FormMethod.Post, new { @Id = "formYardDog" 
    }))
    {
        //Loop to Get Dictionary List Buttons with Distinct Section Values.
        for (int i = 0; i < Model.Continent.Count; i++)
        {
         
            //If the SectionCity is not null, then back-ground color to red.

            if (@Model.Continent[i].Value == SectionCity)
            {

                <button name="@Html.NameFor(model => model.Continent)" 
         value="@Model.Continent[i].Value" id="ddlSectionButtons" runat="server" , new { 
        class="ddlSectionButtons" onClick="focusMe(this);" style="background-color: 
       #AFE1AF; color: black" }>@Model.Continent[i].Value</button>

            }
            else
            {
                // var Count = i;
                <!--  <input type="submit" name="Html.NameFor(model => model.Section)" 
       value=Model.Section[i].Value id="ddlSectionButtons" , new { 
     class="ddlSectionButtons" onClick="focusMe(this);" }/> -->
                <button name="@Html.NameFor(model => model.Continent)" 
    value="@Model.Continent[i].Value" id="ddlSectionButtons" runat="server" , new { 
       class="ddlSectionButtons" onClick="focusMe(this);" 
      }>@Model.Continent[i].Value</button>

            }

        }
        if (SectionCity == null || SectionCity == "")
        {
            <button text="All" type="submit" name="@Html.NameFor(model => 
     model.Continent)" , new { onClick="focusMe(this);" id="ddlSectionAllButton" 
   class="ddlSectionAllButton" placeholder="All" style="background-color: #AFE1AF; 
      color: black" })>All</button>
        }
        else
        {
            <button text="All" type="submit" name="@Html.NameFor(model => 
    model.Continent)" , new { onClick="focusMe(this);" id="ddlSectionAllButton" 
     class="ddlSectionButtons" placeholder="All" style="background-color: #045AC6; 
      color: white" })>All</button>
        }

        <br />
        //Section == Country    //ContinentLocation == PlantLocation
        @Html.DropDownListFor(model => model.Continent, Model.Continent, "- Continent - 
      ", new { onchange = "document.forms[0].submit();", @id = "ddlWarehouses", @class = 
      "ddlWarehouses" })
        @Html.DropDownListFor(model => model.Country, Model.Country, " - Country -", new 
      { onchange = "document.forms[0].submit();", @id = "ddlSection", @class = 
      "ddlSection" })

        <div id="content">
            @webGrid.GetHtml(tableStyle: "webgrid-table",
                                headerStyle: "webgrid-header",
                                footerStyle: "webgrid-footer",
                     //alternatingRowStyle: "webgrid-alternating-row",
                     selectedRowStyle: "webgrid-selected-row",
                                rowStyle: "webgrid-row-style",
                                  mode: WebGridPagerModes.All,
                      htmlAttributes: new { @id = "webGrid" },
                     columns: webGrid.Columns(
                     webGrid.Column(header: "Actions", format:@<span class="link">
<a href="#" class="collapse expand-btn">Expand</a>
<a href="#" class="expand collapse-btn">Collapse</a>
<!--
<a class="Edit" href="javascript:;">Edit</a>
<a class="Clear" href="javascript:;">Clear</a>
<a class="Update" href="javascript:;" style="display:none">Update</a>
<a class="Cancel" href="javascript:;" style="display:none">Cancel</a>
    -->
 </span>),

   webGrid.Column(columnName: "Country", header: "Country", format: @<div>
            <label id="ContinentLbl" class="label"><a id="CountryNameLnk" 
    href="javascript:;">@item.ContinentName</a></label>
            <!--- <input id="Location" class="text" type="text" value="item.CountryName" 
     style="display:none" onkeyup="this.value = this.value.toUpperCase();"/> -->
            <br />
            <label id="CountryLbl" ><a id="RegioNameLnk" href="#" class="collapse 
  expand-btn" >@item.CountryName</a></label>
            <br />
            <label id="RegionLbl"  ><a id="CityNameLnk"href="#" class="collapse expand- 
   btn" >@item.RegionName</a></label>
            <br />
            <label id="CityLbl" class="expand collapse" >@item.CityName</label>
        </div>, style: "CountryName"),

   webGrid.Column(header: "RowPageID", format: @<div>
  <label id="LocationIDLbl" class="label">@item.LocationID</label>
 </div>, style: "LocationID"))),

            <div id="RowCountBpttom"><b>Records: @firstRecord - @lastRecord of 
   @webGrid.TotalRowCount</b></div>
        </div>
        <br />
        <div class="WebGridTable">
        </div>
    }
</form>

<script type="text/javascript" 
  src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript" 
   src="https://ajax.cdnjs.com/ajax/libs/json2/20110223/json2.js"></script>
<script src="~/Scripts/YardDog.js"></script>
<script type="text/javascript">
    //Expand the Nodes. 
    $(function () {
        $('.expand').hide();      // Default - hide the table row of Course information
        // and also hide the Collapse text from
        // Student information's action column

        $('.expand-btn, .collapse-btn').on("click", function () {
            var tr = $(this).parents('tr:first');
            tr.find('.expand, .collapse').toggle();    // toggle to display either 
  Expand or
            // Collapse text in the Student row

            tr.next().toggle();    // toggle to display table row with Course 
 information
        });
    });
</script>

  </body>
linq

1条答案

按热度按时间
pxiryf3j

pxiryf3j1#

若要获取自定义投影作为查询结果，可以将其选择到匿名类，也可以为此创建另一个DTO。
修剪的例子（由于你的代码是一个截图-我不能复制它）：

var customProjection = from c in entities.DimLocations
                       join f in entities.FactInventories
                       on c.LocationId equals f.LocationId
                       select new {
                           c.ContinentName,
                           f.ProductPrice
                       }

这样，您就可以访问不同表中的字段。

赞(0）分享  回复(0）举报  2023-02-17
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com