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 Query: Mehrfache AND und OR in einer Abfrage erstellen

07.10.2021 (👁25174)


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

        }

 

    }

}