Using LinQ to access multiple tables. What is the correct way to join these tables with LinQ to be the same as SQL Query?

huangapple
go评论97阅读模式
英文:

Using LinQ to access multiple tables. What is the correct way to join these tables with LinQ to be the same as SQL Query?

问题

I am trying to access all the tables in the data warehouse that I created for an experimental project using Lin-Q and I just don't seem to understand how to access every table regarding the data structure, but can easily query using SQL Server to get my results. I can access the DimLocation table, but none of the others. I think in the controller is the right approach, but don't know how to select each column of every table using Lin-Q. Any help would be greatly appreciated!

My Program: [GlobalCommandCenter][1]

SQL Query Result Join Tables:

[![enter image description here][2]][2]

Tables To Join From Entities Framework:
[![enter image description here][3]][3]

LinQ I Created to Join Tables (Need help to join all tables as SQL):
[![enter image description here][4]][4]

I can use one table and it works just fine, but I get this error when I try to do multiple tables as Michael describes to do.
[![enter image description here][5]][5]

Added multiple joins code in the controller:

  1. LocationDatas = (List<DimLocation>)(from c in entities.DimLocations
  2.              join f in entities.FactInventories
  3.              on c.LocationId equals f.LocationId
  4.              join p in entities.DimProductInventories
  5.              on f.ProductId equals p.ProductId
  6.              join dd in entities.DimDates
  7.              on f.DateSK equals dd.DateSK
  8.              join dt in entities.DimTimes
  9.              on f.TimeSK equals dt.TimeSK
  10.              where c.ContinentName == continent && c.CountryName == country 
  11.              && c.CountryName == c.CountryName && c.ContinentName == c.ContinentName 
  12.              && c.ContinentName == continent || string.IsNullOrEmpty(continent) || string.IsNullOrEmpty(country) 
  13.              && c.ContinentName == continent
  14.              select new 
  15.              {
  16.                  c.LocationId,
  17.                  c.ContinentName,
  18.                  c.CountryName,
  19.                  c.RegionName,
  20.                  c.CityName,
  21.                  p.ProductName,
  22.                  f.ProductPrice,
  23.                  f.ProductQty,
  24.                  dd.StandardDate,
  25.                  dt.StandardTime
  26.              }),

Error I get after adding multiple joins code:
[![enter image description here][6]][6]

I edited the call to the model, did away with the bracket and added a semicolon at the very end of it. I then added var to each output with a semi colon at the very end of each statement. After that I get errors on the view.
[![Error Controller Problem][7]][7]
Compilation Error on View (IEnumerable Compatible Error in Razor View?):
[![Compilation Error on View][8]][8]

SQL Query:

  1. SELECT ContinentName, CountryName, RegionName, CityName, ProductName, 
  2. finv.ProductPrice, 
  3. finv.ProductQty, StandardDate, StandardTime 
  4. FROM DimLocation dloc
  5. INNER JOIN FactInventory finv ON finv.LocationId = dloc.LocationId
  6. INNER JOIN DimProductInventory dprod ON finv.ProductId = dprod.ProductId
  7. INNER JOIN DimDate ddat ON finv.DateSK = ddat.DateSK
  8. INNER JOIN DimTime dtim ON finv.TimeSK = dtim.TimeSK
  10. SELECT * FROM FactInventory   
  11. SELECT * FROM DimLocation
  12. SELECT * FROM DimDate            
  13. SELECT * FROM DimTime
  14. SELECT * FROM DimProductInventory

Controller:

  1. using ClassLibraryDAL.DAL;
  2. using OperationsCommandCenter.Models;
  3. using System;
  4. using System.Collections.Generic;
  5. using System.Linq;
  6. using System.Web;
  7. using System.Web.Mvc;
  9. namespace OperationsCommandCenter.Controllers
  10. {
  11.     public class HomeController : Controller
  12.     {
  13.         // GET: Home
  14.         public ActionResult Index()
  15.         {
  16.             CommandCenterModel model = PopulateModel(null, null);
  18.             return View(model);
  19.         }
  21.         //Controls DropDownList.
  22.         [HttpPost]
  23.         public ActionResult Index(string continent, string country)
  24.         {
  25.             CommandCenterModel model = PopulateModel(continent, country);
  27.             return View(model);
  28.         }
  30.         //Population Controller. References list selection.
  31.         public static CommandCenterModel PopulateModel(string continent, string country)
  32.         {
  33.             using (GlobalCommandCenter3Entities entities = new GlobalCommandCenter3Entities())
  34.             {
  35.                 CommandCenterModel model = new CommandCenterModel()
  36.                 {
  37.                     //Join all the tables together and extract the information into one web grid. 
  38.                     LocationDatas = (from c in entities.DimLocations
  39.                                      join f in entities.FactInventories
  40.                                      on c.LocationId equals f.LocationId
  42.                                      join p in entities.DimProductInventories
  43.                                      on f.ProductId equals p.ProductId
  45.                                      join dd in entities.DimDates
  46.                                      on f.DateSK equals dd.DateSK
  48.                                      join dt in entities.DimTimes
  49.                                      on f.TimeSK equals dt.TimeSK
  50.                                      where c.ContinentName == continent && c.CountryName == country && c.CountryName == c.CountryName 
  51.                                      && c.ContinentName == c.ContinentName && c.ContinentName == continent || string.IsNullOrEmpty(continent) || 
  52.                                      string.IsNullOrEmpty(country) && c.ContinentName == continent
  53.                                      select  c).ToList(),  
  55.                     Country = (from c in entities.DimLocations
  56.                                orderby c.CountryName
  57.                                where !string.IsNullOrEmpty(c.CountryName) && c.CountryName != null && continent == c.ContinentName
  58.                                select new SelectListItem { Text = c.CountryName, Value = c.CountryName }).Distinct().ToList(),
  60.                     Continent = (from c in entities.DimLocations
  61.                                          orderby c.ContinentName
  62.                                          where (c.ContinentName == c.ContinentName)
  63.                                          select new SelectListItem { Text = c.ContinentName, Value = c.ContinentName }).Distinct().ToList(),
  64.                     /*
  65.                     Location = (from c in entities.DimLocations
  66.                                      join f in entities.FactInventories
  67.                                      on c.LocationId equals f.LocationId
  68.                                      join p in entities.DimProductInventories
  69.                                      on f.ProductId equals p.ProductId
  70.                                      join dd in entities.DimDates
  71.                                      on f.DateSK equals dd.DateSK
  72.                                      join dt in entities.DimTimes
  73.                                      on f.TimeSK equals dt.TimeSK
  74.                                      where c.ContinentName == continent && c.CountryName == country && c.CountryName == c.CountryName 
  75.                                     && c.ContinentName == c.ContinentName && c.ContinentName == continent || string.IsNullOrEmpty(continent) || 
  76.                                     string.IsNullOrEmpty(country) && c.ContinentName == continent
  77.                                      select new { c.ContinentName, c.CountryName, c.RegionName, c.CityName, f.ProductPrice, 
  78.                                     f.ProductQty, dd.StandardDate, dt.StandardTime }).ToList(),
  79.                     */
  81.                 };
  82.                 //Allow to stay on selection. 
  83.                 model.SectionCity = continent;
  84.                 return model;
  85.             }
  86.         }
  87.     }
  88. }

Model:

  1. using ClassLibraryDAL.DAL;
  2. using System;
  3. using System.Collections.Generic;
  4. using System.Linq;
  5. using System.Web;
  6. using System.Web.Mvc;
  8. namespace OperationsCommandCenter.Models
  9. {
  10.     public class CommandCenterModel
  11.     {
  12.         public List<DimLocation> LocationDatas { get; set; }
  13.         public List<DimProductInventory> ProductInventory { get; set; }
  14.         public List<SelectListItem> Country { get; set; } 
  15.         public List<SelectListItem> Continent { get; set; } 
  17.         public List<SelectListItem> ProductName { get;
  19. <details>
  20. <summary>英文:</summary>
  22. I am trying to access all the tables in the data warehouse that I created for an experimental project using Lin-Q and I just don&#39;t seem to understand how to access every table regarding the data structure, but can easily query using SQL Server to get my results.  I can access the DimLocation table, but none of the others. I think in the controller is the right approach, but don&#39;t know how to select each column of every table using Lin-Q. Any help would be greatly appreciated!
  24. My Program: [GlobalCommandCenter][1]
  27. SQL Query Result Join Tables:
  29. [![enter image description here][2]][2]
  31. Tables To Join From Entities Framework:
  32. [![enter image description here][3]][3]
  34. LinQ I Created to Join Tables (Need help to join all tables as SQL): 
  35. [![enter image description here][4]][4]
  37. I can use one table and it works just fine, but I get this error when I try to do multiple tables as Michael describes to do. 
  38. [![enter image description here][5]][5]
  40. Added multiple joins code in the controller:
  42.           			LocationDatas = (List&lt;DimLocation&gt;)(from c in entities.DimLocations
  43. 							 join f in entities.FactInventories
  44. 							 on c.LocationId equals f.LocationId
  45. 							 join p in entities.DimProductInventories
  46. 							 on f.ProductId equals p.ProductId
  47. 							 join dd in entities.DimDates
  48. 							 on f.DateSK equals dd.DateSK
  49. 							 join dt in entities.DimTimes
  50. 							 on f.TimeSK equals dt.TimeSK
  51. 							 where c.ContinentName == continent &amp;&amp; c.CountryName == country 
  52. 							 &amp;&amp; c.CountryName == c.CountryName &amp;&amp; c.ContinentName == c.ContinentName 
  53. 							 &amp;&amp; c.ContinentName == continent || string.IsNullOrEmpty(continent) || string.IsNullOrEmpty(country) 
  54. 							 &amp;&amp; c.ContinentName == continent
  55. 							 select new 
  56. 							 {
  57. 								 c.LocationId,
  58. 								 c.ContinentName,
  59. 								 c.CountryName,
  60. 								 c.RegionName,
  61. 								 c.CityName,
  62. 								 p.ProductName,
  63. 								 f.ProductPrice,
  64. 								 f.ProductQty,
  65. 								 dd.StandardDate,
  66. 								 dt.StandardTime
  67. 							 }),
  69. Error I get after adding multiple joins code:
  70. [![enter image description here][6]][6]
  72. I edited the call to the model, did away with the bracket and added a semicolon at the very end of it. I then added var to each output with a semi colon at the very end of each statement. After that I get errors on the view. 
  73. [![Error Contoller Problem][7]][7]
  74. Compilation Error on View (IEnumerable Compatible Error in Razor View?):
  75. [![Compilation Error on View][8]][8]
  77.     public static CommandCenterModel PopulateModel(string continent, string country)
  78.     {
  79.     	using (GlobalCommandCenter3Entities entities = new GlobalCommandCenter3Entities())
  80.     	{
  81.     		**CommandCenterModel model = new CommandCenterModel()
  82.     		{**
  83.     			//Join all the tables together and extract the information into one web grid. 
  84.     			LocationDatas = (from c in entities.DimLocations
  85.     							 join f in entities.FactInventories
  86.     							 on c.LocationId equals f.LocationId
  87.     							
  88.     							 join p in entities.DimProductInventories
  89.     							 on f.ProductId equals p.ProductId
  90.     							 
  91.     							 join dd in entities.DimDates
  92.     							 on f.DateSK equals dd.DateSK
  93.     
  94.     							 join dt in entities.DimTimes
  95.     							 on f.TimeSK equals dt.TimeSK
  96.     							 where c.ContinentName == continent &amp;&amp; c.CountryName == country &amp;&amp; c.CountryName == c.CountryName 
  97.     							 &amp;&amp; c.ContinentName == c.ContinentName &amp;&amp; c.ContinentName == continent || string.IsNullOrEmpty(continent) 
  98.     							 || string.IsNullOrEmpty(country) &amp;&amp; c.ContinentName == continent
  99.                                  select  c).ToList(),	 
  100.     
  101.     			Country = (from c in entities.DimLocations
  102.     					   orderby c.CountryName
  103.     					   where !string.IsNullOrEmpty(c.CountryName) &amp;&amp; c.CountryName != null &amp;&amp; continent == c.ContinentName
  104.     					   select new SelectListItem { Text = c.CountryName, Value = c.CountryName }).Distinct().ToList(),
  105.      
  106.     
  107.     			Continent = (from c in entities.DimLocations
  108.     								 orderby c.ContinentName
  109.     								 where (c.ContinentName == c.ContinentName)
  110.     								 select new SelectListItem { Text = c.ContinentName, Value = c.ContinentName }).Distinct().ToList(),
  111.     			/*
  112.     			Location = (from c in entities.DimLocations
  113.     							 join f in entities.FactInventories
  114.     							 on c.LocationId equals f.LocationId
  115.     							 join p in entities.DimProductInventories
  116.     							 on f.ProductId equals p.ProductId
  117.     							 join dd in entities.DimDates
  118.     							 on f.DateSK equals dd.DateSK
  119.     							 join dt in entities.DimTimes
  120.     							 on f.TimeSK equals dt.TimeSK
  121.     							 where c.ContinentName == continent &amp;&amp; c.CountryName == country &amp;&amp; c.CountryName == c.CountryName &amp;&amp; c.ContinentName == c.ContinentName &amp;&amp; c.ContinentName == continent || string.IsNullOrEmpty(continent) || string.IsNullOrEmpty(country) &amp;&amp; c.ContinentName == continent
  122.     							 select new { c.ContinentName, c.CountryName, c.RegionName, c.CityName, f.ProductPrice, f.ProductQty, dd.StandardDate, dt.StandardTime }).ToList(),
  123.     			*/
  124.     
  125.     		};
  126.     		//Allow to stay on selection. 
  127.     		model.SectionCity = continent;
  128.     		return model;
  129.     	}
  130.     }
  132. SQL Query:
  135. 				
  136.         SELECT ContinentName, CountryName, RegionName, CityName, ProductName, 
  137.         finv.ProductPrice, 
  138.         finv.ProductQty, StandardDate, StandardTime 
  139.         FROM DimLocation dloc
  140.         INNER JOIN FactInventory finv ON finv.LocationId = dloc.LocationId
  141.         INNER JOIN DimProductInventory dprod ON finv.ProductId = dprod.ProductId
  142.         INNER JOIN DimDate ddat ON finv.DateSK = ddat.DateSK
  143.         INNER JOIN DimTime dtim ON finv.TimeSK = dtim.TimeSK
  146.                     SELECT * FROM FactInventory   
  147. 					SELECT * FROM DimLocation
  148. 					SELECT * FROM DimDate            
  149. 					SELECT * FROM DimTime
  150. 					SELECT * FROM DimProductInventory
  154. Controller:
  156.         using ClassLibraryDAL.DAL;
  157.         using OperationsCommandCenter.Models;
  158.         using System;
  159.         using System.Collections.Generic;
  160.         using System.Linq;
  161.         using System.Web;
  162.         using System.Web.Mvc;
  164.         namespace OperationsCommandCenter.Controllers
  165.        {
  166. 	    public class HomeController : Controller
  167. 	   {
  168. 		// GET: Home
  169. 		public ActionResult Index()
  170. 		{
  171. 			CommandCenterModel model = PopulateModel(null, null);
  173. 			return View(model);
  174. 		}
  176. 		//Controls DropDownList.
  177. 		[HttpPost]
  178. 		public ActionResult Index(string continent, string country)
  179. 		{
  180. 			CommandCenterModel model = PopulateModel(continent, country);
  182. 			return View(model);
  183. 		}
  185. 		//Population Controller. References list selection.
  186. 		public static CommandCenterModel PopulateModel(string continent, string country)
  187. 		{
  188. 			using (GlobalCommandCenter3Entities entities = new 
  189.             GlobalCommandCenter3Entities())
  190. 			{
  191. 				CommandCenterModel model = new CommandCenterModel()
  192. 				{
  193. 					//Join all the tables together and extract the information into one web 
  194.                       grid. 
  195. 					LocationDatas = (from c in entities.DimLocations
  196. 									 join f in entities.FactInventories
  197. 									 on c.LocationId equals f.LocationId
  198. 									 
  199. 									 join p in entities.DimProductInventories
  200. 									 on f.ProductId equals p.ProductId
  201. 									 
  202. 									 join dd in entities.DimDates
  203. 									 on f.DateSK equals dd.DateSK
  205. 									 join dt in entities.DimTimes
  206. 									 on f.TimeSK equals dt.TimeSK
  207. 									 where c.ContinentName == continent &amp;&amp; c.CountryName == 
  208.             country &amp;&amp; c.CountryName == c.CountryName &amp;&amp; c.ContinentName == c.ContinentName 
  209.             &amp;&amp; c.ContinentName == continent || string.IsNullOrEmpty(continent) || 
  210.             string.IsNullOrEmpty(country) &amp;&amp; c.ContinentName == continent
  211. 									 select  c).ToList(),  
  213. 					Country = (from c in entities.DimLocations
  214. 							   orderby c.CountryName
  215. 							   where !string.IsNullOrEmpty(c.CountryName) &amp;&amp; c.CountryName 
  216.              != null &amp;&amp; continent == c.ContinentName
  217. 							   select new SelectListItem { Text = c.CountryName, Value = 
  218.                                c.CountryName }).Distinct().ToList(),
  221. 					Continent = (from c in entities.DimLocations
  222. 										 orderby c.ContinentName
  223. 										 where (c.ContinentName == c.ContinentName)
  224. 										 select new SelectListItem { Text = c.ContinentName, 
  225.                                          Value = c.ContinentName }).Distinct().ToList(),
  226. 					/*
  227. 					Location = (from c in entities.DimLocations
  228. 									 join f in entities.FactInventories
  229. 									 on c.LocationId equals f.LocationId
  230. 									 join p in entities.DimProductInventories
  231. 									 on f.ProductId equals p.ProductId
  232. 									 join dd in entities.DimDates
  233. 									 on f.DateSK equals dd.DateSK
  234. 									 join dt in entities.DimTimes
  235. 									 on f.TimeSK equals dt.TimeSK
  236. 									 where c.ContinentName == continent &amp;&amp; c.CountryName == 
  237.            country &amp;&amp; c.CountryName == c.CountryName &amp;&amp; c.ContinentName == c.ContinentName 
  238.           &amp;&amp; c.ContinentName == continent || string.IsNullOrEmpty(continent) || 
  239.           string.IsNullOrEmpty(country) &amp;&amp; c.ContinentName == continent
  240. 									 select new { c.ContinentName, c.CountryName, 
  241.            c.RegionName, c.CityName, f.ProductPrice, f.ProductQty, dd.StandardDate, 
  242.            dt.StandardTime }).ToList(),
  243. 					*/
  245. 				};
  246. 				//Allow to stay on selection. 
  247. 				model.SectionCity = continent;
  248. 				return model;
  249. 			}
  250. 		}
  251. 	    }
  252.         }
  254. Model: 
  256.        using ClassLibraryDAL.DAL;
  257.        using System;
  258.        using System.Collections.Generic;
  259.        using System.Linq;
  260.        using System.Web;
  261.        using System.Web.Mvc;
  263.        namespace OperationsCommandCenter.Models
  264.        {
  265. 	   public class CommandCenterModel
  266. 	   {
  267. 		public List&lt;DimLocation&gt; LocationDatas { get; set; }
  268. 		public List&lt;DimProductInventory&gt; ProductInventory { get; set; }
  269. 		public List&lt;SelectListItem&gt; Country { get; set; } 
  270. 		public List&lt;SelectListItem&gt; Continent { get; set; } 
  272. 		public List&lt;SelectListItem&gt; ProductName { get; set; } 
  274. 		//public List&lt;DimLocation&gt; Location { get; set; }
  276. 		public string SectionCity { get; set; }
  278. 	}
  279.     }
  282. View: 
  285.       @model  OperationsCommandCenter.Models.CommandCenterModel
  287.     @{
  288.     ViewBag.Title = &quot;Home Page&quot;;
  289.     string Country = Model.Country.ToString();
  290.     string SectionCity = Model.SectionCity;
  292.      WebGrid webGrid = new WebGrid(source: Model.LocationDatas, canPage: true, canSort: 
  293.        true, sortDirectionFieldName: &quot;ContinentName, CountryName, RegionName, CityName&quot;, 
  294.        rowsPerPage: 20);
  295.     webGrid.Pager(WebGridPagerModes.All);
  297.     }
  299.     &lt;!DOCTYPE html&gt;
  301.     &lt;head&gt;
  302.     &lt;meta name=&quot;viewport&quot; content=&quot;width=device-width&quot; /&gt;
  304.     &lt;title&gt;Global Command Center&lt;/title&gt;
  305.     &lt;link href=&quot;@Url.Content(&quot;~/Content/YardDogStyle.css&quot;)&quot; rel=&quot;stylesheet&quot; type=&quot;text/css&quot; 
  306.     /&gt;
  307.     &lt;link href=&quot;@Url.Content(&quot;~/Scripts/jquery-3.4.1.min.js&quot;)&quot; rel=&quot;stylesheet&quot; 
  308.      type=&quot;text/css&quot; /&gt;
  309.     &lt;link href=&quot;@Url.Content(&quot;~/Scripts/bootstrap.min.js&quot;)&quot; rel=&quot;stylesheet&quot; type=&quot;text/css&quot; 
  310.      /&gt;
  312.     &lt;script src=&quot;~/Scripts/bootstrap.min.js&quot;&gt;&lt;/script&gt;
  313.     &lt;script src=&quot;~/Scripts/jquery-3.4.1.min.js&quot;&gt;&lt;/script&gt;
  314.     &lt;script src=&quot;Scripts/jquery-1.4.1.min.js&quot; type=&quot;text/javascript&quot;&gt;&lt;/script&gt;
  315.     &lt;script src=&quot;~/Scripts/jquery-1.4.1.min.js&quot;&gt;&lt;/script&gt;
  316.    
  317.     &lt;link href=&quot;~/Content/YardDogStyle.css&quot; rel=&quot;stylesheet&quot; /&gt;
  318.     
  319.       &lt;/head&gt;
  320.       &lt;body&gt;
  321.     &lt;div id=&quot;time&quot;&gt;&lt;/div&gt;
  322.     &lt;div id=&quot;RowCount&quot;&gt;&lt;/div&gt;
  323.     &lt;SCRIPT LANGUAGE=&quot;Javascript&quot;&gt;
  325.         function checkTime(i) {
  326.             if (i &lt; 10) {
  327.                 i = &quot;0&quot; + i;
  328.             }
  329.             return i;
  330.         }
  331.         function startTime() {
  332.             var today = new Date();
  333.             var h = today.getHours();
  334.             var m = today.getMinutes();
  335.             var s = today.getSeconds();
  337.             // add a zero in front of numbers&lt;10
  338.             m = checkTime(m);
  339.             s = checkTime(s);
  340.             document.getElementById(&#39;time&#39;).innerHTML = h + &quot;:&quot; + m + &quot;:&quot; + s; //Get the 
  341.             time.
  342.             document.getElementById(&#39;time&#39;).innerHTML = &quot;Date: &quot; + today; //Get the Date.
  343.             t = setTimeout(function () {
  344.                 startTime()
  345.             }, 500);
  346.         }
  347.         startTime();
  348.     &lt;/SCRIPT&gt;
  349.     &lt;script src=&quot;Scripts/jquery-1.4.1.min.js&quot; type=&quot;text/javascript&quot;&gt;&lt;/script&gt;
  350.     &lt;script src=&quot;Scripts/ScrollableGridPlugin.js&quot; type=&quot;text/javascript&quot;&gt;&lt;/script&gt;
  351.     &lt;script type=&quot;text/javascript&quot;&gt;
  352.         $(document).ready(function () {
  353.             $(&#39;#&lt;%=webGrid.ClientID %&gt;&#39;).Scrollable({
  354.                 ScrollHeight: 300
  355.             });
  356.             $(&#39;#&lt;%=webGrid.ClientID %&gt;&#39;).Scrollable({
  357.                 ScrollHeight: 300
  358.             });
  359.         });
  360.     &lt;/script&gt;
  362.     &lt;script src=&quot;Scripts/ScrollableGridPlugin.js&quot; type=&quot;text/javascript&quot;&gt;&lt;/script&gt;
  363.     &lt;script src=&quot;~/Scripts/ScrollableGridPlugin.js&quot;&gt;&lt;/script&gt;
  364.     &lt;script type=&quot;text/javascript&quot;&gt;
  365.         $(document).ready(function () {
  366.             $(&#39;#&lt;%=webGrid.ClientID %&gt;&#39;).Scrollable({
  367.                 ScrollHeight: 300,
  368.                 Width: 467
  369.             });
  370.         });
  371.     &lt;/script&gt;
  372.     &lt;script type=&quot;text/javascript&quot;&gt;
  373.         //Count the rows on the webGrid that are visible... is my intent with this code.
  374.         function CountRows() {
  375.             var totalRowCount = 0;
  376.             var rowCount = 0;
  377.             var gridView = document.getElementById(&quot;&lt;%=webGrid.ClientID %&gt;&quot;);
  378.             var rows = gridView.getElementsByTagName(&quot;tr&quot;)
  379.             for (var i = 0; i &lt; rows.length; i++) {
  380.                 totalRowCount++;
  381.                 if (rows[i].getElementsByTagName(&quot;td&quot;).length &gt; 0) {
  382.                     rowCount++;
  384.                 }
  386.             }
  387.             var message = &quot;Total Row Count: &quot; + totalRowCount;
  388.             message += &quot;\nRow Count: &quot; + rowCount;
  389.             //alert(message);
  390.             message = document.getElementById(&#39;RowCount&#39;).innerHTML = message;
  392.             // return false;
  394.         }
  395.         CountRows();
  396.     &lt;/script&gt;
  397.     &lt;form id=&quot;formYardDog&quot; class=&quot;formYardDog&quot; runat=&quot;server&quot; method=&quot;post&quot;&gt;
  399.         @{ int firstRecord = (webGrid.PageIndex * webGrid.RowsPerPage) + 1;
  400.             int lastRecord = (webGrid.PageIndex * webGrid.RowsPerPage) + webGrid.Rows.Count;
  402.             // webGrid.Rows..GetRowCount(DataGridViewElementStates.Visible);
  403.         }
  404.         &lt;div id=&quot;RowCountTop&quot;&gt;&lt;b&gt;Records: @firstRecord - @lastRecord of 
  405.         @webGrid.TotalRowCount&lt;/b&gt;&lt;/div&gt;&lt;br /&gt;
  407.         @using (Html.BeginForm(&quot;Index&quot;, &quot;Home&quot;, FormMethod.Post, new { @Id = &quot;formYardDog&quot; 
  408.         }))
  409.         {
  410.             //Loop to Get Dictionary List Buttons with Distinct Section Values.
  411.             for (int i = 0; i &lt; Model.Continent.Count; i++)
  412.             {
  413.              
  414.                 //If the SectionCity is not null, then back-ground color to red.
  416.                 if (@Model.Continent[i].Value == SectionCity)
  417.                 {
  419.                     &lt;button name=&quot;@Html.NameFor(model =&gt; model.Continent)&quot; 
  420.              value=&quot;@Model.Continent[i].Value&quot; id=&quot;ddlSectionButtons&quot; runat=&quot;server&quot; , new { 
  421.             class=&quot;ddlSectionButtons&quot; onClick=&quot;focusMe(this);&quot; style=&quot;background-color: 
  422.            #AFE1AF; color: black&quot; }&gt;@Model.Continent[i].Value&lt;/button&gt;
  424.                 }
  425.                 else
  426.                 {
  427.                     // var Count = i;
  428.                     &lt;!--  &lt;input type=&quot;submit&quot; name=&quot;Html.NameFor(model =&gt; model.Section)&quot; 
  429.            value=Model.Section[i].Value id=&quot;ddlSectionButtons&quot; , new { 
  430.          class=&quot;ddlSectionButtons&quot; onClick=&quot;focusMe(this);&quot; }/&gt; --&gt;
  431.                     &lt;button name=&quot;@Html.NameFor(model =&gt; model.Continent)&quot; 
  432.         value=&quot;@Model.Continent[i].Value&quot; id=&quot;ddlSectionButtons&quot; runat=&quot;server&quot; , new { 
  433.            class=&quot;ddlSectionButtons&quot; onClick=&quot;focusMe(this);&quot; 
  434.           }&gt;@Model.Continent[i].Value&lt;/button&gt;
  436.                 }
  438.             }
  439.             if (SectionCity == null || SectionCity == &quot;&quot;)
  440.             {
  441.                 &lt;button text=&quot;All&quot; type=&quot;submit&quot; name=&quot;@Html.NameFor(model =&gt; 
  442.          model.Continent)&quot; , new { onClick=&quot;focusMe(this);&quot; id=&quot;ddlSectionAllButton&quot; 
  443.        class=&quot;ddlSectionAllButton&quot; placeholder=&quot;All&quot; style=&quot;background-color: #AFE1AF; 
  444.           color: black&quot; })&gt;All&lt;/button&gt;
  445.             }
  446.             else
  447.             {
  448.                 &lt;button text=&quot;All&quot; type=&quot;submit&quot; name=&quot;@Html.NameFor(model =&gt; 
  449.         model.Continent)&quot; , new { onClick=&quot;focusMe(this);&quot; id=&quot;ddlSectionAllButton&quot; 
  450.          class=&quot;ddlSectionButtons&quot; placeholder=&quot;All&quot; style=&quot;background-color: #045AC6; 
  451.           color: white&quot; })&gt;All&lt;/button&gt;
  452.             }
  455.             &lt;br /&gt;
  456.             //Section == Country    //ContinentLocation == PlantLocation
  457.             @Html.DropDownListFor(model =&gt; model.Continent, Model.Continent, &quot;- Continent - 
  458.           &quot;, new { onchange = &quot;document.forms[0].submit();&quot;, @id = &quot;ddlWarehouses&quot;, @class = 
  459.           &quot;ddlWarehouses&quot; })
  460.             @Html.DropDownListFor(model =&gt; model.Country, Model.Country, &quot; - Country -&quot;, new 
  461.           { onchange = &quot;document.forms[0].submit();&quot;, @id = &quot;ddlSection&quot;, @class = 
  462.           &quot;ddlSection&quot; })
  464.             &lt;div id=&quot;content&quot;&gt;
  465.                 @webGrid.GetHtml(tableStyle: &quot;webgrid-table&quot;,
  466.                                     headerStyle: &quot;webgrid-header&quot;,
  467.                                     footerStyle: &quot;webgrid-footer&quot;,
  468.                          //alternatingRowStyle: &quot;webgrid-alternating-row&quot;,
  469.                          selectedRowStyle: &quot;webgrid-selected-row&quot;,
  470.                                     rowStyle: &quot;webgrid-row-style&quot;,
  471.                                       mode: WebGridPagerModes.All,
  472.                           htmlAttributes: new { @id = &quot;webGrid&quot; },
  473.                          columns: webGrid.Columns(
  474.                          webGrid.Column(header: &quot;Actions&quot;, format:@&lt;span class=&quot;link&quot;&gt;
  475.     &lt;a href=&quot;#&quot; class=&quot;collapse expand-btn&quot;&gt;Expand&lt;/a&gt;
  476.     &lt;a href=&quot;#&quot; class=&quot;expand collapse-btn&quot;&gt;Collapse&lt;/a&gt;
  477.     &lt;!--
  478.     &lt;a class=&quot;Edit&quot; href=&quot;javascript:;&quot;&gt;Edit&lt;/a&gt;
  479.     &lt;a class=&quot;Clear&quot; href=&quot;javascript:;&quot;&gt;Clear&lt;/a&gt;
  480.     &lt;a class=&quot;Update&quot; href=&quot;javascript:;&quot; style=&quot;display:none&quot;&gt;Update&lt;/a&gt;
  481.     &lt;a class=&quot;Cancel&quot; href=&quot;javascript:;&quot; style=&quot;display:none&quot;&gt;Cancel&lt;/a&gt;
  482.         --&gt;
  483.      &lt;/span&gt;),
  485.        webGrid.Column(columnName: &quot;Country&quot;, header: &quot;Country&quot;, format: @&lt;div&gt;
  486.                 &lt;label id=&quot;ContinentLbl&quot; class=&quot;label&quot;&gt;&lt;a id=&quot;CountryNameLnk&quot; 
  487.         href=&quot;javascript:;&quot;&gt;@item.ContinentName&lt;/a&gt;&lt;/label&gt;
  488.                 &lt;!--- &lt;input id=&quot;Location&quot; class=&quot;text&quot; type=&quot;text&quot; value=&quot;item.CountryName&quot; 
  489.          style=&quot;display:none&quot; onkeyup=&quot;this.value = this.value.toUpperCase();&quot;/&gt; --&gt;
  490.                 &lt;br /&gt;
  491.                 &lt;label id=&quot;CountryLbl&quot; &gt;&lt;a id=&quot;RegioNameLnk&quot; href=&quot;#&quot; class=&quot;collapse 
  492.       expand-btn&quot; &gt;@item.CountryName&lt;/a&gt;&lt;/label&gt;
  493.                 &lt;br /&gt;
  494.                 &lt;label id=&quot;RegionLbl&quot;  &gt;&lt;a id=&quot;CityNameLnk&quot;href=&quot;#&quot; class=&quot;collapse expand- 
  495.        btn&quot; &gt;@item.RegionName&lt;/a&gt;&lt;/label&gt;
  496.                 &lt;br /&gt;
  497.                 &lt;label id=&quot;CityLbl&quot; class=&quot;expand collapse&quot; &gt;@item.CityName&lt;/label&gt;
  498.             &lt;/div&gt;, style: &quot;CountryName&quot;),
  500.        webGrid.Column(header: &quot;RowPageID&quot;, format: @&lt;div&gt;
  501.       &lt;label id=&quot;LocationIDLbl&quot; class=&quot;label&quot;&gt;@item.LocationID&lt;/label&gt;
  502.      &lt;/div&gt;, style: &quot;LocationID&quot;))),
  504.                 &lt;div id=&quot;RowCountBpttom&quot;&gt;&lt;b&gt;Records: @firstRecord - @lastRecord of 
  505.        @webGrid.TotalRowCount&lt;/b&gt;&lt;/div&gt;
  506.             &lt;/div&gt;
  507.             &lt;br /&gt;
  508.             &lt;div class=&quot;WebGridTable&quot;&gt;
  509.             &lt;/div&gt;
  510.         }
  511.     &lt;/form&gt;
  513.     &lt;script type=&quot;text/javascript&quot; 
  514.       src=&quot;https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js&quot;&gt;&lt;/script&gt;
  515.     &lt;script type=&quot;text/javascript&quot; 
  516.        src=&quot;https://ajax.cdnjs.com/ajax/libs/json2/20110223/json2.js&quot;&gt;&lt;/script&gt;
  517.     &lt;script src=&quot;~/Scripts/YardDog.js&quot;&gt;&lt;/script&gt;
  518.     &lt;script type=&quot;text/javascript&quot;&gt;
  519.         //Expand the Nodes. 
  520.         $(function () {
  521.             $(&#39;.expand&#39;).hide();      // Default - hide the table row of Course information
  522.             // and also hide the Collapse text from
  523.             // Student information&#39;s action column
  525.             $(&#39;.expand-btn, .collapse-btn&#39;).on(&quot;click&quot;, function () {
  526.                 var tr = $(this).parents(&#39;tr:first&#39;);
  527.                 tr.find(&#39;.expand, .collapse&#39;).toggle();    // toggle to display either 
  528.       Expand or
  529.                 // Collapse text in the Student row
  531.                 tr.next().toggle();    // toggle to display table row with Course 
  532.      information
  533.             });
  534.         });
  535.     &lt;/script&gt;
  538.       &lt;/body&gt;
  541.   [1]: https://drive.google.com/file/d/1DP9vY4RdS9LcJJWqDt6G-DtRgKsEnOo2/view
  542.   [2]: https://i.stack.imgur.com/hwhSH.jpg
  543.   [3]: https://i.stack.imgur.com/A1i94.jpg
  544.   [4]: https://i.stack.imgur.com/e4fDA.jpg
  545.   [5]: https://i.stack.imgur.com/vlZ4y.jpg
  546.   [6]: https://i.stack.imgur.com/mN7p0.jpg
  547.   [7]: https://i.stack.imgur.com/v94A5.jpg
  548.   [8]: https://i.stack.imgur.com/2Jeto.jpg
  550. </details>
  553. # 答案1
  554. **得分**: 0
  556. 获取自定义投影作为查询结果，您可以选择将其选择为匿名类或创建另一个DTO用于此。
  558. 由于您的代码是截图，无法复制，因此以下是修剪过的示例：
  560. ```csharp
  561. var customProjection = from c in entities.DimLocations
  562.                        join f in entities.FactInventories
  563.                        on c.LocationId equals f.LocationId
  564.                        select new {
  565.                            c.ContinentName,
  566.                            f.ProductPrice
  567.                        }

通过这种方式，您可以访问来自不同表的字段。

英文:

To get a custom projection as a result of the query you can either select it to an anonymous class or create another DTO for this.

Trimmed example (due to your code being a screenshot - I can't copy it):

  1. var customProjection = from c in entities.DimLocations
  2.                        join f in entities.FactInventories
  3.                        on c.LocationId equals f.LocationId
  4.                        select new {
  5.                            c.ContinentName,
  6.                            f.ProductPrice
  7.                        }

This way you can access fields from different tables.

huangapple
  • 本文由 发表于 2023年2月14日 05:05:40
  • 转载请务必保留本文链接：https://go.coder-hub.com/75441185.html
  • asp.net-mvc
  • c#
  • linq
  • linq-to-entities
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定