我正在使用web Api/AngularJs/Entity Framework 6将Windows Forms应用程序迁移到web环境中。该应用程序具有一个报告功能,基本上用户可以从一组报告中进行选择,并创建一个查询,按日期、ID范围和状态过滤数据。下面显示的方法构建SELECT/WHERE语句,并将结果作为绑定到datagridview的数据表返回,因此代码非常通用且灵活。我想把它迁移到EF,我想知道什么是最好的方法。。。1.创建表示每个select语句(主体)的接口和DTO?2.如何将where子句标记为EF lambda表达式?这是最好的选择吗?
我对如何以优雅高效的方式处理这项任务持开放态度。
public DataTable GetReportData(ReportArgs rArgs)
{
string strSql = string.Empty;
string whereClause = string.Format(" WHERE {0} {1}",GetDateRange(rArgs), GetIdRange(rArgs));
string fechaI = rArgs.MatchesFechaI ? string.Format("'{0}'", rArgs.FechaI.ToShortDateString()) : "NULL";
string fechaF = rArgs.MatchesFechaF == EnumCompararFecha.igual ? string.Format("'{0}'", rArgs.FechaF.ToShortDateString()) : "NULL";
try
{
switch ((EnumReportes)rArgs.ReportNumber)
{
case EnumReports.GainTotal:
strSql = string.Format("SELECT Id,DateInitial as [Date Initial], WeightInitial as [Weight Initial], DateFinal AS [Date Final], Weightfinal as [Weight Final], Gainday as Gain FROM RepGainTotal {0} ", whereClause);
break;
...
case EnumReports.GainByPeriod:
// In this case the Where clause is built inside of buildStringGainPeriodo(rArgs)
strSql = buildStringGainPeriodo(rArgs);
SetRepGainsPeriodo(rArgs, strSql);
strSql = strSql + " ORDER BY 2";
break;
case EnumReports.Bulls:
strSql = string.Format("SELECT Id,Weight,Date1,Date2,Date3,LastGain,Total as [G. Total],round(WeightHoy,0) as [Weight Hoy] FROM ANALISIS_CEBA {0} ", whereClause);
break;
case EnumReports.SalesVsbuys:
strSql = string.Format("SELECT Id,Date_Sale as [Date Sale],Weight_Sale as [Weight Sale],Precio_kilo as [Precio Kilo]," +
"Date_buy as [Date buy], Kilos_buy as [Weight (c)],cost_kilo as [cost Kilo], " +
"GainDailyWeights as [G/day (Weights)],GainDailyGrams as [G/day (Grams)] " +
" FROM SalesVsbuys {0} ", whereClause);
SetRepSalesVsbuys(rArgs, whereClause);
break;
case EnumReports.SalesByPeriod:
strSql = string.Format("SELECT * FROM Sales {0} ", whereClause);
SetRepSales(rArgs, whereClause);
break;
case EnumReports.BuysByPeriodo:
strSql = string.Format("SELECT * FROM buys {0} ", whereClause);
SetRepbuys(rArgs, whereClause);
break;
case EnumReports.TasksByPeriod:
strSql = string.Format("SELECT Date, Task, TotalFROM RepMovimientos {0} ", whereClause);
SetRepMvtos(rArgs, whereClause);
break;
case EnumReports.WeightsByPeriod:
strSql = string.Format("SELECT Date, Id, Weight FROM WeightHistory {0} order by Date,ID", whereClause);
SetRepPesajes(rArgs, whereClause);
break;
}
}
}
我找到了一个解决方案:使用EF SqlQuery方法加载表示视图的DTO。因此,我仍然可以使用ad-hoc sql的灵活性,然后我可以使用linq、数据绑定等:
"string strSql=字符串。格式("从RepLifetimeGain中选择InitialDate等");string whereClause=WhereClauseBuilder。BuildWhereClause(rArgs);
使用(GlContext gContext=新GlContext()){var gvida=等待gContext。RepLifetimeGain。SqlQuery(string.Format("{0}{1}",strSql,whereClause))。ToListAsync();
}