与DBML LINQ中的Where子句中的比较日期的问题[英] Issue with compare dates in where clause in dbml linq

本文是小编为大家收集整理的关于与DBML LINQ中的Where子句中的比较日期的问题的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我在应用程序中使用DBML进行LINQ搜索.我设计了与本文相关的DynamicLibrary.cs的高级搜索查询:
带有Linq的动态查询

因此,我创建一个DBML并将其用于我的高级LINQ搜索,而无需额外连接到数据库.除date datatype外,我对数据类型没有任何问题.

这是我使用它的whereClause()函数.

private string whereClause()
{
    string strWhere = string.Empty;
    if (rbvalveStreet.Checked)
    {
        if (!string.IsNullOrEmpty(valveStreet.Text))
        {
            strWhere += " valveStreet.Contains(\"" + valveStreet.Text + "\") AND ";
        }
    }

    if (rbhealth.Checked)
    {
        strWhere += " health = " + health.SelectedIndex.ToString() + " AND ";
    }
    if (rbleak.Checked)
    {
        strWhere += " leak = " + leak.SelectedIndex.ToString() + " AND ";
    }
    if (chbDateRange.Checked)
    {
        string fromDate = clsEasy.makeDate8((DateTime)dtsFrom.Value); //ex: 2018/07/30
        string toDate = clsEasy.makeDate8((DateTime)dtsTo.Value); //ex: 2018/08/05
        strWhere += " washdate >= " + fromDate + "  && washdate <= "+ toDate + " AND ";
    }
    string[] remove = { " AND " };
    foreach (string item in remove)
    {
        if (strWhere.EndsWith(item))
        {
            strWhere = strWhere.Substring(0, strWhere.LastIndexOf(item));
            break; //only allow one match at most
        }
    }

    return strWhere;
}

我想在检查chbdaterange时添加到strWhere linq查询,并在日期之间进行搜索.但是我得到了这个错误:
错误1 [2]
washdate列的数据类型是Date,我尝试以下代码:

DateTime fromDate =  DateTime.Parse(clsEasy.makeDate8((DateTime)dtsFrom.Value));
DateTime toDate = DateTime.Parse(clsEasy.makeDate8((DateTime)dtsTo.Value));
strWhere += " washdate >= " + fromDate + "  && washdate <= "+ toDate + " AND ";


并得到另一个错误:
错误2

我确定这里发生了数据类型冲突.请帮助我解决这个问题.谢谢.

编辑:

clsEasy.makeDate8((DateTime)dtsFrom.Value) retrun date as string for example "2018/07/30"

编辑2:
这就是我将whereclause()用于linq dbml的方式:

private void btnFind_Click(object sender, EventArgs e)
    {
        string wClause = this.whereClause();
        if (!string.IsNullOrEmpty(wClause))
        {
            string address = "Data Source=.;Initial Catalog=valveManagement2018;Integrated Security=True";
            dgv.DataSource = null;
            refreshData();
            contextLinqDataContext cxt = new contextLinqDataContext(address);
            var query = cxt.tblValveExpirations
                        .Where(wClause);
            _dt = null;
            _dt = LINQToDataTable(query);
            refreshForm();
            countRows();
        }
        else
        {
            refreshData();
            refreshForm();
            countRows();
        }
    }

推荐答案

您无需创建SQL字符串即可动态添加过滤器.您可以将多个.where()调用链接起来,以创建相当于(Condition1 && Condition2 && Condition3)

的等效.

假设原始查询存储在称为query的变量中,您可以写下这样的内容:

IQueryable<MyEntity> query = ....;

if (rbvalveStreet.Checked && !string.IsNullOrEmpty(valveStreet.Text))
{
    var searchText=valveStreet.Text;
    query=query.Where(item=>item.valveStreet.Contains(searchText);
}
if (rbhealth.Checked)
{
    //Do we really want the *indexes*?
    //SHouldn't this be the selected text/value?
    var healthIdx=health.SelectedIndex;
    query = query.Where(itme=>item.health = healthIdx);
}
if (rbleak.Checked)
{
    var leakIdx=leak.SelectedIndex;
    query = query.Where(item=>item.leak = leakIdx);
}
if (chbDateRange.Checked)
{
    //No need to cast if this is a DateTimePicker control
    DateTime fromDate = dtsFrom.Value; 
    DateTime toDate = dtsTo.Value; 

    query = query.Where(item=> item.washdate >=fromDate
                            && item.washdate<= toDate)
}

最终query将与AND操作员

结合使用所有指定标准

本文地址:https://www.itbaoku.cn/post/1557216.html

问题描述

I'm using DBML to linq search in my application. I Designed advanced search queries with DynamicLibrary.cs related to this article:
Dynamic query with Linq
So I Create a dbml and use it for my advanced linq search without extra connecting to the database. I don't have any problem with datatypes except date datatype.

This is my whereClause() function that I use it.

private string whereClause()
{
    string strWhere = string.Empty;
    if (rbvalveStreet.Checked)
    {
        if (!string.IsNullOrEmpty(valveStreet.Text))
        {
            strWhere += " valveStreet.Contains(\"" + valveStreet.Text + "\") AND ";
        }
    }

    if (rbhealth.Checked)
    {
        strWhere += " health = " + health.SelectedIndex.ToString() + " AND ";
    }
    if (rbleak.Checked)
    {
        strWhere += " leak = " + leak.SelectedIndex.ToString() + " AND ";
    }
    if (chbDateRange.Checked)
    {
        string fromDate = clsEasy.makeDate8((DateTime)dtsFrom.Value); //ex: 2018/07/30
        string toDate = clsEasy.makeDate8((DateTime)dtsTo.Value); //ex: 2018/08/05
        strWhere += " washdate >= " + fromDate + "  && washdate <= "+ toDate + " AND ";
    }
    string[] remove = { " AND " };
    foreach (string item in remove)
    {
        if (strWhere.EndsWith(item))
        {
            strWhere = strWhere.Substring(0, strWhere.LastIndexOf(item));
            break; //only allow one match at most
        }
    }

    return strWhere;
}

I want add to strWhere a linq query with search between to dates when chbDateRange is Checked. But I get this error:
 error 1[2]
Also washdate column's datatype is Date and I try the following code either:

DateTime fromDate =  DateTime.Parse(clsEasy.makeDate8((DateTime)dtsFrom.Value));
DateTime toDate = DateTime.Parse(clsEasy.makeDate8((DateTime)dtsTo.Value));
strWhere += " washdate >= " + fromDate + "  && washdate <= "+ toDate + " AND ";


and get this another error:
error 2

I'm sure a data type conflict occurred here. please Help me to resolve this problem. Thanks.

Edited:

clsEasy.makeDate8((DateTime)dtsFrom.Value) retrun date as string for example "2018/07/30"

Edited 2:
This is how I use whereClause() for Linq DBML:

private void btnFind_Click(object sender, EventArgs e)
    {
        string wClause = this.whereClause();
        if (!string.IsNullOrEmpty(wClause))
        {
            string address = "Data Source=.;Initial Catalog=valveManagement2018;Integrated Security=True";
            dgv.DataSource = null;
            refreshData();
            contextLinqDataContext cxt = new contextLinqDataContext(address);
            var query = cxt.tblValveExpirations
                        .Where(wClause);
            _dt = null;
            _dt = LINQToDataTable(query);
            refreshForm();
            countRows();
        }
        else
        {
            refreshData();
            refreshForm();
            countRows();
        }
    }

推荐答案

You don't need to create a SQL string to add filters dynamically. You can chain multiple .Where() calls to create the equivalent of (Condition1 && Condition2 && Condition3).

Assuming the original query is stored in a variable called query you could write something like this :

IQueryable<MyEntity> query = ....;

if (rbvalveStreet.Checked && !string.IsNullOrEmpty(valveStreet.Text))
{
    var searchText=valveStreet.Text;
    query=query.Where(item=>item.valveStreet.Contains(searchText);
}
if (rbhealth.Checked)
{
    //Do we really want the *indexes*?
    //SHouldn't this be the selected text/value?
    var healthIdx=health.SelectedIndex;
    query = query.Where(itme=>item.health = healthIdx);
}
if (rbleak.Checked)
{
    var leakIdx=leak.SelectedIndex;
    query = query.Where(item=>item.leak = leakIdx);
}
if (chbDateRange.Checked)
{
    //No need to cast if this is a DateTimePicker control
    DateTime fromDate = dtsFrom.Value; 
    DateTime toDate = dtsTo.Value; 

    query = query.Where(item=> item.washdate >=fromDate
                            && item.washdate<= toDate)
}

The final query will have all specified criteria combined with an AND operator