存储过程使用联接从一张表中选择所有字段
当使用跳过扩展处理多对多关系时，我对从 linq 到 sql 感到非常沮丧。 它不允许我使用连接查询。 不确定 SQL Server 2005 是否属于这种情况，但我目前正在使用 SQL Server 2000。
现在我考虑编写一个存储过程来获取与两个表匹配的表，例如 Album_Photo (Album->Album_Photo<-Photo) 和照片表并且只需要照片数据，因此我将相册的 ID 与 Album_Photo 进行匹配，并使用该 ID 来匹配照片。 在存储过程中，我只是获取所有连接的数据。 之后，在 linq to sql 中，我创建一个新的 Album 对象。
例如，
var albums = (from r in result
where (modifier_id == r.ModifierID || user_id == r.UserID)
select new Album() {
Name = r.Name,
UserID = r.UserID,
ModifierID = r.ModifierID,
ID = r.ID,
DateCreated = r.DateCreated,
Description = r.Description,
Filename = r.Filename
}).AsQueryable();
我使用 AsQueryable 来获取结果作为 IQueryable 而不是 IEnumerable。 后来我想对集合做一些事情，它给了我这个错误：
System.InvalidOperationException：查询结果不能被枚举多次。
I am very frustrated from linq to sql when dealing with many to many relationship with the skip extension. It doesn't allow me to use joinned queries. Not sure it is the case for SQL server 2005 but I am currently using SQL Server 2000.
Now I consider to write a store procedure to fetch a table that is matched by two tables e.g. Album_Photo (Album->Album_Photo<-Photo) and Photo table and only want the Photos data so I match the Album's ID with Album_Photo and use that ID to match the photo. In the store procedure I am just fetch all the joinned data. After that in the linq to sql, I create a new Album object.
e.g.
var albums = (from r in result
where (modifier_id == r.ModifierID || user_id == r.UserID)
select new Album() {
Name = r.Name,
UserID = r.UserID,
ModifierID = r.ModifierID,
ID = r.ID,
DateCreated = r.DateCreated,
Description = r.Description,
Filename = r.Filename
}).AsQueryable();
I used the AsQueryable to get the result as a IQueryable rather than IEnumerable. Later I want to do something with the collection, it gives me this error:
System.InvalidOperationException: The query results cannot be enumerated more than once.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
听起来您遇到的情况是，当您稍后想要在代码中过滤查询时，查询已经执行了。
您可以做类似... var albums = (blah blah blah).AsQueryable().Where(filterClause) 的事情吗
当您有足够的信息来处理
稍后在代码中尝试 albums.where(filter) 时会发生什么时， ？ 这就是你正在尝试的吗？
It sounds like you have a situation where the query has already executed by the time you are want to filter it later in your code.
Can you do something like...
var albums = (blah blah blah).AsQueryable().Where(filterClause) when you have enough info to process
what happens if you try albums.where(filter) later on in the code? Is this what you are trying?