IQueryable查询数据库问题

[machaoqun6666]

_LearnerRepository = new LearnerRepository(new GAS_VeristaEntities());
var learner = _LearnerRepository.GetThemAll();
_ClassInStudentRepository = new ClassInStudentRepository(new GAS_VeristaEntities());
var cisr = _ClassInStudentRepository.GetThemAll();

上面从数据库中取出两个表的所有行。

表结构类似
learner表包括列
LearnerID LearnerName ...
101
102
103

cisr表包括列
ClassinStudentID LearnerID ReceipNo
1 101 s
2 102 a
3 103 s

现在给出ReceipNo，获取对应的learner表的相应行。

if (!string.IsNullOrEmpty(searchString5)) //searchString5 为ReceiptNo
{
cisr = cisr.Where(u => u.RecieptNo == searchString5);
foreach (var id in cisr)
{
learner = learner.Where(u => u.LearnerID == id.LearnerID);
}
}
这样只能取出一行数据，如果查询ReceiptNo为s,怎么查出全部行


if (!string.IsNullOrEmpty(searchString5))
{
cisr = cisr.Where(u => u.RecieptNo == searchString5);
bool firsttime = true;
_LearnerRepository1 = new LearnerRepository(new GAS_VeristaEntities());
foreach (var id in cisr)
{
var learner1 = _LearnerRepository1.GetThemAll();
if (firsttime)
{
learner = learner.Where(u => u.LearnerID == id.LearnerID);
firsttime = false;
}
else
{
learner = learner.Union(learner1.Where(u => u.LearnerID == id.LearnerID));
}
}
firsttime = true;
}
这样试过，但learner类型有变化，后续
public PaginatedList(IQueryable<T> source, int pageIndex, int pageSize)
{
PageIndex = pageIndex;
PageSize = pageSize;
TotalCount = source.Count(); -------------这里出错。{"The specified LINQ expression contains references to queries that are associated with different contexts."}

TotalPages = (int)Math.Ceiling(TotalCount / (double)PageSize);

this.AddRange(source.Skip(PageIndex * PageSize).Take(PageSize));
}

[threenewbee]

if (!string.IsNullOrEmpty(searchString5)) //searchString5 为ReceiptNo { cisr = cisr.Where(u => u.RecieptNo == searchString5); foreach (var id in cisr) { learner = learner.Where(u => u.LearnerID == id.LearnerID); } } 这样只能取出一行数据，如果查询ReceiptNo为s,怎么查出全部行 if (!string.IsNullOrEmpty(searchString5)) //searchString5 为ReceiptNo { learner = cisr.Where(u => u.RecieptNo == searchString5).Select(x => learner.Where(u => u.LearnerID == x.LearnerID)); } TotalCount = source.ToList().Count();

[machaoqun6666]

learner = cisr.Where(u => u.RecieptNo == searchString5).Select(x => learner.Where(u => u.LearnerID == x.LearnerID)); 无法将类型“System.Linq.IQueryable<System.Linq.IQueryable<StaffManagement.Models.T_TMS_LearnToSkate_Learners>>”隐式转换为“System.Linq.IQueryable<StaffManagement.Models.T_TMS_LearnToSkate_Learners>”。存在一个显式转换(是否缺少强制转换?) 两个表返回类型 public IQueryable<T_TMS_LearnToSkate_Learners> GetThemAll() { return Entities.T_TMS_LearnToSkate_Learners; } public IQueryable<T_TMS_LearnToSkate_ClassInStudent> GetThemAll() { return Entities.T_TMS_LearnToSkate_ClassInStudent; } 如果加（IQueryable<T_TMS_LearnToSkate_Learners>）强制转换编译通过 但运行出现Unable to cast object of type 'System.Data.Objects.ObjectQuery`1[System.Linq.IQueryable`1[StaffManagement.Models.T_TMS_LearnToSkate_Learners]]' to type 'System.Linq.IQueryable`1[StaffManagement.Models.T_TMS_LearnToSkate_Learners]'.

[threenewbee]

learner = cisr.Where(u => u.RecieptNo == searchString5).SelectMany(x => learner.Where(u => u.LearnerID == x.LearnerID));

[wanghui0380]

咋又是这问题呢，昨天有人问了一个一模一样滴 还是一样的回答，join，groupjoin，selectmany都可以。 代码不写，希望你能自己写出来。东西不难，你无非不知道上面3个一般情况很少用的关键词（其实把，这3个东西也是经常用滴，只是因为可以用循环套，所以即使不知道他们，了不起多写几行代码）

[machaoqun6666]

TotalCount = source.ToList().Count(); A cycle was detected in a LINQ expression.

[machaoqun6666]

查找函数的代码 public ActionResult Index(string currentFilter1, string searchString1, string currentFilter2, string searchString2, string currentFilter3, string searchString3, string currentFilter4, string searchString4, string currentFilter5, string searchString5, Nullable<int> page) { _LearnerRepository = new LearnerRepository(new GAS_VeristaEntities()); var learner = _LearnerRepository.GetThemAll(); _ClassInStudentRepository = new ClassInStudentRepository(new GAS_VeristaEntities()); var cisr = _ClassInStudentRepository.GetThemAll(); _IDTypeRepository = new IDTypeRepository(new GAS_VeristaEntities()); var r = _IDTypeRepository.GetThemAll().OrderBy(c => c.IDTypeID); ViewData["Type"] = new SelectList(r, "IDTypeID", "IDType"); if (Request.HttpMethod == "GET") { searchString1 = currentFilter1; searchString2 = currentFilter2; searchString3 = currentFilter3; searchString4 = currentFilter4; searchString5 = currentFilter5; } else { page = 0; } ViewBag.CurrentFilter1 = searchString1; ViewBag.CurrentFilter2 = searchString2; ViewBag.CurrentFilter3 = searchString3; ViewBag.CurrentFilter4 = searchString4; ViewBag.CurrentFilter5 = searchString5; DateTime rdate; //int s4,s5,s6; if (!string.IsNullOrEmpty(searchString1)) { learner = learner.Where(u => u.FullName.IndexOf(searchString1)!=-1); } if (!string.IsNullOrEmpty(searchString2)) { learner = learner.Where(u => u.NRICOrPassportNo.IndexOf(searchString2) != -1); } if (!string.IsNullOrEmpty(searchString3)) { try { rdate = Convert.ToDateTime(searchString3); learner = learner.Where(u => u.RegistrationDate == rdate); } catch { } } if (!string.IsNullOrEmpty(searchString4)) { int id; id = Convert.ToInt32(searchString4); learner = learner.Where(u => u.IDType== id); } if (!string.IsNullOrEmpty(searchString5)) { learner = cisr.Where(u => u.RecieptNo == searchString5).SelectMany(x => learner.Where(u => u.LearnerID == x.LearnerID)); } var endl = learner.OrderByDescending(c => c.RegistrationDate).AsPagination(page ?? 0, 6); return View(endl); }

[wanghui0380]

给你个连接，一次性“充好电” http://www.cnblogs.com/lyj/archive/2008/01/24/1051495.html 希望你能自己写出来

[machaoqun6666]

public PaginatedList(IQueryable<T> source, int pageIndex, int pageSize) { PageIndex = pageIndex; PageSize = pageSize; TotalCount = source.ToList().Count(); TotalPages = (int)Math.Ceiling(TotalCount / (double)PageSize); this.AddRange(source.Skip(PageIndex * PageSize).Take(PageSize)); }

[threenewbee]

引用 5 楼 machaoqun6666 的回复:

TotalCount = source.ToList().Count(); A cycle was detected in a LINQ expression.

你的source有问题。

[threenewbee]

不要把query作为闭包变量在查询内再使用，应该先ToList()立刻执行。

[threenewbee]

引用 7 楼 wanghui0380 的回复:

给你个连接，一次性“充好电” http://www.cnblogs.com/lyj/archive/2008/01/24/1051495.html 希望你能自己写出来

这个文章很详细，很好。

[machaoqun6666]

学习了，多谢二位。