如何基于特定条件动态构建Linq查询
本文关键字:Linq 查询 构建 动态 何基于 特定条件 | 更新日期: 2023-09-27 18:12:40
我有一个数据输入表单,用户将输入DateFrom和DateTo字段。
Select From Date: <input type="text" id="datepickerfrom" name="datepickerfrom"/>
Select To Date: <input type="text" id="datepickerto" name="datepickerto"/>
<asp:Button ID="btnGetData" runat="server" OnClick="BtnGetData_Click" Text="Get Error List" />
我想建立一个Linq查询,在没有提供输入的情况下只检索前100条记录。
如果用户提供了DateFrom而没有提供DateTo,用户将选择在DateFrom到DateTime.Now之间的大于DateFrom的数据。
如果用户提供了DateTo而不提供DateFrom,用户将选择小于DateTo的数据。
我现在有以下内容:
public static List<ErrorLogData> GetLogErrorData(string appName, InputData data)
{
SqlConnection con;
List<ErrorLogData> errorLogData = null;
string query = "";
if (data.DateFrom == "" && data.DateTo == "")
{
query += "from ld in logData.errorLogs.Take(10000)";
}
if (data.DateFrom == "" && data.DateTo != "")
{
query += "from ld in logData.errorLogs where ld.errorTime <= " + data.DateTo;
}
if (data.DateFrom != "" && data.DateTo == "")
{
query += "from ld in logData.errorLogs where ld.errorTime >= " + data.DateFrom + " && <= " + DateTime.Now;
}
if (data.DateFrom != "" && data.DateTo != "")
{
query += "from ld in logData.errorLogs where ld.errorTime >= " + data.DateFrom + " && <= " + data.DateTo;
}
DateTime dateFrom = Convert.ToDateTime(data.DateFrom);
DateTime dateTo = Convert.ToDateTime(data.DateTo);
using (con = new SqlConnection(ConfigurationManager.AppSettings[conKey]))
using (WebEntities logData = new WebEntities())
{
logData.CommandTimeout = 300;
var errorLog = query +
select new ErrorLogData
{
ErrorID = ld.errorID,
ErrorTime = ld.errorTime,
UserName = ld.username,
ErrorType = ld.errorType,
Error = ld.error,
ControlNumber = ld.controlNumber
};
errorLogData = errorLog.ToList();
}
return errorLogData;
}
我不确定如何将query
附加到"select new ErrorLogData...
"语句以获得整个查询。
这里是什么方法?
假设您正在使用某种LINQ数据访问技术,请使用如下内容:
private List<Entity> GetData(DateTime? dateFrom, DateTime? dateTo)
{
IQueryable<Entity> query = ...; //Here reference your table
if (dateFrom == null && dateTo == null)
{
query = query.Take(100);
}
else
{
DateTime dateToValue = dateTo ?? DateTime.Now;
query = query.Where(x => x.Date <= dateToValue);
if (dateFrom != null)
{
query = query.Where(x => x.Date >= dateFrom.Value);
}
}
return query.ToList(); //This will actually execute the query. Here you can expand your query to select specific columns before executing ToList
}
您应该能够使用错误日志的IQueryable结果,然后为您的if语句执行lambda表达式。
List<ErrorLogData> errorLogData = null;
DateTime dateFrom = Convert.ToDateTime(data.DateFrom);
DateTime dateTo = Convert.ToDateTime(data.DateTo);
//IQueryable errorLog
var errorLog = from ld in logData.errorLogs
select new ErrorLogData
{
ErrorID = ld.errorID,
ErrorTime = ld.errorTime,
UserName = ld.username,
ErrorType = ld.errorType,
Error = ld.error,
ControlNumber = ld.controlNumber
};
if (data.DateFrom == "" && data.DateTo == "")
{
errorLogData = errorLog.Take(10000);
}
if (data.DateFrom == "" && data.DateTo != "")
{
errorLogData = errorLog.where(x => x.ErrorTime <= dateTo).ToList();
//query += "from ld in logData.errorLogs where ld.errorTime <= " + data.DateTo;
}
//contine to implement If
return errorLogData;