Readdy Write  
0,00 €
Your View Money
Views: Count
Self 20% 0
Your Content 60% 0

Users by Links 0
u1*(Content+Views) 10% 0
Follow-Follower 0
s2*(Income) 5% 0

Count
Followers 0
Login Register as User
linq ; WHERE ;

LINQ: ForEach Loop in Linq Query zum Erstellen von WHERE oder Unterabfragen

07.10.2021 (👁1666)


Code Beispiel zum erstellen von mehreren WHERE Bedingungen in einer LINQ Abfrage

Dabei wird hier gezeigt, wie man eine mehrfache AND Verknüpfung in einen LINQ SQL Text einbindet.

Es wird dabei verhindrt, dass viele einzelne Abfragen an den SQL Server gesendet werden sondern nur eine einzige SQL Abfrage, welche die WHERE Bedingung mit AND zu mehreren einfachen Unterbedingungen zusammenfasst

Linq C# Unterabfragen, ForEach Loop, Entity Framework EF zu SQL Server

 

//--< connect_db >--

var query_ProductDates = (from tblProductDates in _dbContext.tbl_ProductDates

select tblProductDates);

//--</ connect_db >--

 

//-< SubQuery.SelectIDs >-

foreach (int ProductID in setProductIDs)

{

    query_ProductDates=query_ProductDates.Where(q => q.IDProduct == ProductID);

}

sSQL_Result = query_ProductDates.ToQueryString(); //SQL string

//-</ SubQuery.SelectIDs >-

 

foreach(var row in query_ProductDates)

{

    sSQL_Result = sSQL_Result + "\n" + row.IDProduct + " " + row.Date_Product;

}

SQL Text send to Server

DECLARE @__ProductID_0 int = 2;

DECLARE @__ProductID_1 int = 1;

 

SELECT [t].[IDProduct], [t].[Date_Product]

FROM [tblProductDates] AS [t]

WHERE ([t].[IDProduct] = @__ProductID_0) AND ([t].[IDProduct] = @__ProductID_1)

 

 

"DECLARE @__ProductID_0 int = 2;\r\nDECLARE @__ProductID_1 int = 1;\r\n\r\nSELECT [t].[IDProduct], [t].[Date_Product]\r\nFROM [tblProductDates] AS [t]\r\nWHERE ([t].[IDProduct] = @__ProductID_0) AND ([t].[IDProduct] = @__ProductID_1)"

 

 

 

API 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)

        {

//-------------< UpdateGridCell() >-------------

string sSQL_Result = "";

 

//< get items >

String[] arrParameter_SetIDs = param_SetItems.setids.Split(";");

String[] arrParameter_SetMonths = param_SetItems.setmonths.Split(";");

var SetDay = param_SetItems.setday;

//</ get items >

 

//< convert >

List<int> setProductIDs = new List<int>();

foreach (string sID in arrParameter_SetIDs)

{

    setProductIDs.Add(Convert.ToInt32(sID));

}

List<DateTime> setMonths = new List<DateTime>();

foreach (string sMonth in arrParameter_SetMonths)

{

    setMonths.Add(Convert.ToDateTime(sMonth));

}

//</ convert >

 

//--< connect_db >--

var query_ProductDates = (from tblProductDates in _dbContext.tbl_ProductDates

select tblProductDates);

//--</ connect_db >--

 

//-< SubQuery.SelectIDs >-

foreach (int ProductID in setProductIDs)

{

    query_ProductDates=query_ProductDates.Where(q => q.IDProduct == ProductID);

}

sSQL_Result = query_ProductDates.ToQueryString(); //SQL string

//-</ SubQuery.SelectIDs >-

 

foreach(var row in query_ProductDates)

{

    sSQL_Result = sSQL_Result + "\n" + row.IDProduct + " " + row.Date_Product;

}

 

return Ok(sSQL_Result);

//-------------</ UpdateGridCell() >-------------

        }

 

    }

}