Dieser C# Code zeigt, wie man eine SQL Server Tabelle mit LINQ nach mehrfachen AND Bedingungen und nach mehrfachen OR Bedingungen zusammenführt.
Beispiel Linq EF Code
C# Code EF Entity Framework, API Controller in asp.net core MVC
//***SCRIPT ZUM DURCHLAUFEN von ProductDates mit MULTI OR AND AND //*LINQ.ANY //*LINQ.WHERE.WHERE (AND) string sSQL_Result = ""; //--< connect_db >-- var query_ProductDates = (from tblProductDates in _dbContext.tbl_ProductDates select tblProductDates); //--</ connect_db >--
//-< SubQuery.SelectIDs >- //*add OR into WHERE Statement query_ProductDates = query_ProductDates.Where(q => setProductIDs.Any(IDProduct => IDProduct == q.IDProduct)); //-</ SubQuery.SelectIDs >-
//-< SubQuery.Months >- //*add OR into WHERE Statement query_ProductDates = query_ProductDates.Where(q => setMonths.Any(dtMonth => dtMonth == q.Date_Product)); //-</ SubQuery.Months >-
//< #logger > sSQL_Result = query_ProductDates.ToQueryString(); //SQL string sSQL_Result = sSQL_Result + "\n"; foreach (var r in query_ProductDates) { sSQL_Result = sSQL_Result + "\n" + r.IDProduct; } //</ #logger >
|
Controller Code:
using Microsoft.AspNetCore.Http; using Microsoft.AspNetCore.Mvc; using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; using MyApp.Models; using DevExtreme.AspNet.Data; using DevExtreme.AspNet.Mvc; using MyApp.Models.DBContext; using MvcContrib.UI.DataList; using System.Data; using Newtonsoft.Json; using Microsoft.EntityFrameworkCore;
namespace MyApp.Controllers.Api {
[Route("api/[controller]")] [ApiController] public class apiDemoController : ControllerBase { #region Controller Init private readonly ApplicationDbContext _dbContext;
public apiDemoController(ApplicationDbContext dbContext) { //----< Init: Controller >---- _dbContext = dbContext; //----</ Init: Controller >---- } #endregion
[HttpGet("getGridData")] public object getGridData(DataSourceLoadOptions loadOptions, string filterGridParameters) { //-------------< getDataGrid() >-------------
var dsProducts = from tblProducts in _dbContext.tbl_Products orderby tblProducts.Product ascending select tblProducts;
var dsProductDates = from tblProductDates in _dbContext.tbl_ProductDates orderby tblProductDates.IDProduct, tblProductDates.Date_Product ascending select tblProductDates;
//--< Get Linq.Query >-- DataTable tblMonth = new DataTable(); //< define Columns > tblMonth.Columns.Add("IDProduct", typeof(int)); tblMonth.Columns.Add("Product", typeof(String));
DateTime dtFrom = DateTime.Today.AddDays(- DateTime.Today.Day +1 ); DateTime dtTo= DateTime.Today.AddMonths(6); DateTime dtLoop = dtFrom; while (dtLoop <= dtTo) { tblMonth.Columns.Add(dtLoop.ToString("yyyy-MM-01"), typeof(String)); dtLoop = dtLoop.AddMonths(1); } //</ define Columns >
//----< @Loop:Products >---- foreach (var Product in dsProducts) { //----< [ Product ]---- DataRow row = tblMonth.Rows.Add(); row["IDProduct"] = Product.IDProduct; row["Product"] = Product.Product;
//*SubQuery: Current Dates of Row Product var qDatesByProduct = dsProductDates.Where(p => p.IDProduct == Product.IDProduct && p.Date_Product>= dtFrom && p.Date_Product<=dtTo) .Select(d=>new { dtMonth=d.Date_Product}) ; //----< @Loop:Products >---- foreach (var ProductDate in qDatesByProduct) { //----< [ Product ]---- string sMonth01 = ProductDate.dtMonth.ToString("yyyy-MM-01"); if (row[sMonth01] is System.DBNull) { row[sMonth01] = ProductDate.dtMonth.ToString("yyyy-MM-dd"); } else { row[sMonth01] = row[sMonth01] + ", " + ProductDate.dtMonth.ToString("yyyy-MM-dd"); } //----</ [ Product ]---- } //----</ @Loop:Products >----
//----</ [ Product ]---- } //----</ @Loop:Products >----
List<DataRow> dataList = tblMonth.Select().ToList(); //----< fill Data_to_View >---- //---< @Loop: Rows >---
//----</ fill Data_to_View >---- return JsonConvert.SerializeObject(tblMonth) ; //-------------</ getDataGrid() >------------- }
[HttpPut("UpdateGridCell")] public ActionResult UpdateGridCell([FromForm] Dictionary<string,string> cell) { //-------------< UpdateGridCell() >------------- var sKey = cell["key"]; //ID=A var sCell_Column_Value = cell["values"]; //{"..":".."}
sCell_Column_Value = sCell_Column_Value.Substring(1,sCell_Column_Value.Length-2); //"..":".." string[] arrCell_Column_Value = sCell_Column_Value.Split(':');
string sColumn_Name = arrCell_Column_Value[0]; //"col: 2021-10-01" string sCell_Value = arrCell_Column_Value[1]; //"val: 2021-10-10;2021-10-20;" sCell_Value = sCell_Value.Replace("\"",""); DateTime dtCell = DateTime.Parse(sCell_Value);
int IDProduct = Convert.ToInt32(sKey);
ProductDateModel productDate = _dbContext.tbl_ProductDates.SingleOrDefault(p => p.IDProduct == IDProduct && p.Date_Product==dtCell); if(productDate == null) { try { //< new ProductDate > ProductDateModel newProductDate = new ProductDateModel(); newProductDate.Date_Product = dtCell; newProductDate.IDProduct = IDProduct; _dbContext.tbl_ProductDates.Add(newProductDate); //*add 1 record //</ new ProductDate >
_dbContext.SaveChanges(); //*update all in table
//----< Save Data >---- try { //_dbContext.Update(newProductDate); _dbContext.SaveChanges(); } catch (DbUpdateConcurrencyException) { return Content("Update Error"); } //----</ Save Data >----
//else //{ // //< Add on Server > // _dbContext.tbl_Notes.Add(note); // _dbContext.SaveChanges(); // IDNote = note.IDNote; // //</ Add on Server > //} //await _dbContext.SaveChangesAsync(true); } catch (DbUpdateConcurrencyException ex) { return Content("Error in saving ID:" + IDProduct + " Date:" + productDate.Date_Product + ".. " + ex.Message); }
}
return Ok(); //-------------</ UpdateGridCell() >------------- }
//*Upload Data public class Upload_Item { public string setids { get; set; } public string setmonths { get; set; } public string setday { get; set; } }
[HttpPost("Add_Date_To_Products")] public ActionResult Add_Date_To_Products([FromBody] Upload_Item param_SetItems) { //-------------< Add_Date_To_Products() >------------- //< get items > String[] arrParameter_SetIDs = param_SetItems.setids.Split(";"); String[] arrParameter_SetMonths = param_SetItems.setmonths.Split(";"); int intSetDay = Convert.ToInt32( param_SetItems.setday) ; //</ get items >
//< convert > List<int> setProductIDs = new List<int>(); foreach (string sID in arrParameter_SetIDs) { setProductIDs.Add(Convert.ToInt32(sID)); }
//*add month target-days
List<DateTime> setMonths = new List<DateTime>(); foreach (string sMonth in arrParameter_SetMonths) { DateTime dtMonth = Convert.ToDateTime(sMonth); DateTime dtSet= dtMonth.AddDays(intSetDay-1); //#test //if (dtMonth > DateTime.Parse("2022-02-01")) { //*must be at the same month ex.:2021-02-30 if (dtSet.Month == dtMonth.Month) { setMonths.Add(dtSet); } } } //</ convert >
//----< @Loop: Products >---- foreach ( int IDProduct in setProductIDs) { foreach (DateTime dtMonthDay in setMonths) { //< add > ProductDateModel productDate = _dbContext.tbl_ProductDates.FirstOrDefault(x => x.IDProduct == IDProduct && x.Date_Product == dtMonthDay); if (productDate == null) { productDate = new ProductDateModel(); productDate.IDProduct = IDProduct; productDate.Date_Product = dtMonthDay; _dbContext.tbl_ProductDates.Add(productDate); //</ add >
//----< Save Data >---- try { //_dbContext.Update(productDate); _dbContext.SaveChanges(true); } catch (Exception ex) { //return Content("Insert Date Error"); Console.WriteLine(ex.Message); } //----</ Save Data >---- } } } //----</ @Loop: Products >---- //----</ INSERT NEW DAYS >-----
return Ok(); //-------------</ Add_Date_To_Products() >------------- }
} }
|