dbml linq中的where子句中的比较日期问题 [英] Issue with compare dates in where clause in dbml linq
问题描述
我正在使用DBML对应用程序进行linq搜索.我用DynamicLibrary.cs
设计了与本文相关的高级搜索查询:
使用Linq进行动态查询
因此,我创建了一个dbml并将其用于我的高级linq搜索,而无需额外连接到数据库.除date
数据类型外,我对数据类型没有任何问题.
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.
这是我使用的whereClause()
函数.
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;
}
我想向linq查询添加到strWhere
中,并在chbDateRange被选中的日期之间进行搜索.但我收到此错误:
同样,washdate
列的数据类型是Date
,我也尝试以下代码:
I want add to strWhere
a linq query with search between to dates when chbDateRange is Checked. But I get this error:
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:
我确定这里发生了数据类型冲突.请帮助我解决此问题.谢谢.
I'm sure a data type conflict occurred here. please Help me to resolve this problem. Thanks.
已编辑:
clsEasy.makeDate8((DateTime)dtsFrom.Value) retrun date as string for example "2018/07/30"
这就是我对Linq DBML使用whereClause()的方式:
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();
}
}
推荐答案
您无需创建SQL字符串即可动态添加过滤器.您可以链接多个.Where()调用来创建(Condition1 && Condition2 && Condition3)
的等效项.
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)
.
假设原始查询存储在名为query
的变量中,您可以编写如下内容:
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)
}
最后的query
将具有所有指定的条件并与AND
运算符组合
The final query
will have all specified criteria combined with an AND
operator
这篇关于dbml linq中的where子句中的比较日期问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!