Freelance Project Requests info@CodeDocu.de Software Development in C# WPF Asp.Net Core Vba Excel Word SQL-Server EF Linq, UWP Net
#

 

Code example for creating multiple WHERE conditions in a LINQ query

Here it is shown how to integrate a multiple AND link into a LINQ SQL text.

this prevents many individual queries from being sent to the sql server but only a single sql query that combines the where condition with and into several simple subconditions

 

Linq C# Subqueries, ForEachLoop, Entity Framework EF to 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() >-------------

        }

 

    }

}

 

 

 

 

 

 

Mobile

.

Contact for Jobs, Project Requests: raimund.popp@microsoft-programmierer.de