SQL形如下文:
Select * from Table where Table.id in (1,2,3,4)
Linq 形如下文:
Int[] ids = {1,2,3,4};
from Table
where ids.Contains(Table.id)
Linq to SQL是可以通过的,但是Linq to Entity在3.5 SP1中会报错,只能等到4.0.
两个解决办法,
1.
使用ObjectQuery
但做了Join后报错还需要再看看。
2.
增加一个方法以实现OR效果
解决办法参见:http://blogs.msdn.com/alexj/archive/2009/03/26/tip-8-writing-where-in-style-queries-using-linq-to-entities.aspx
代码参见:
///
<summary>
///
检索InterlinearData
///
</summary>
private List<InterlinearInfo> RetrieveInterlinearData()
{
int[] columns = GetShowInterlinearColumns();
if (columns == null || columns.Length == 0) return null;
string columnCondition = string.Join(",", columns.Select(c=>c.ToString()).ToArray());
if (!navigator.IsShownManuscriptVersification) navigator.IsShownManuscriptVersification = true;
using (iEntities iContext = new iEntities(ServiceLocator.GetUniqueDBInfo(DatabaseType.IDatabase).EntityConnectionString))
{
//TODO 只取了当前BCV的数据,需要改为范围
//Entity Framework 不支持范围“columns.Contains(col.ColumnID)”,需要改为SQL
//string query = "SELECT C.ColumnID, C.ColumnText,C.ManuscriptID FROM Interlinear AS C WHERE C.ColumnID IN (" + columnCondition + ")";
//(new ObjectQuery<Interlinear>(query, iContext)
return (from col in iContext.Interlinear.Where(BuildOrExpression<Interlinear, int>( p => p.ColumnID, columns))
join manu in iContext.view_Manuscript.Where(manuscript=>manuscript.BookNumber == navigator.CurrentBookID
&& manuscript.ChapterNumber == navigator.CurrentChapterID
&& manuscript.VerseNumber == navigator.CurrentVerserID)
on col.ManuscriptID equals manu.ManuscriptID
select new InterlinearInfo(){ ColumnID = col.ColumnID, ColumnText = col.ColumnText, ManuscriptID = col.ManuscriptID}
).ToList();
}
} |