ASP.NET MVC3 中的 LINQ 自加入

发布于 2024-11-09 20:01:21 字数 1128 浏览 5 评论 0原文

我遇到一种情况，需要在 LINQ 中的表上进行自连接。该表由 ItemsID、Title、SeriesTitle 等字段组成。一个项目可以是系列或成员，我可以通过查看上面有“S”或“M”字母的 ItemId 来判断。我需要检索属于 ItemId“S117”的系列成员的所有记录。我可以通过下面的代码在简单的 SQL 中执行此操作，

select  i.Series_Title, i.Item_ID, i2.Item_ID as Member_ID, 
        i2.Title as Member_Title, i2.Series_Title as Member_Series_Title 
        from Items i join Items i2 on i.Series_Title = i2.Series_Title
        where i.Item_ID = "S117"

现在，我在 LINQ 中翻译了此查询，如下所示：

items = _dataContext.Items.AsQueryable();
items = from series in items 
        join members in items on series.Series_Title.ToLower() 
        equals members.Series_Title.ToLower()
        where series.Item_ID.ToLower().Equals(itemId)
        select series;

此查询的最后一行 select series 将仅检索系列而不是成员，我还需要成员。我正在使用 MVC3 Razor 视图，我必须显示几乎所有字段，因此我没有使用 select new {....} 即使当我尝试使用 select new {series, Members} 时，我也遇到了这个异常 -

无法隐式转换类型 'System.Linq.IQueryable' 到“System.LinQ.IQueryable” 存在显式转换。

任何建议将不胜感激。

原文

I have a situation where I need to do a self join on a table in LINQ. The table consists of fields ItemsID, Title, SeriesTitle, and many other. An item can be either a series or members and I can tell that by looking into ItemId which has "S" or "M" letters on it. I need to retrieve all records that are member of a series with ItemId "S117". I can do this in simple SQL by the code below,

select  i.Series_Title, i.Item_ID, i2.Item_ID as Member_ID, 
        i2.Title as Member_Title, i2.Series_Title as Member_Series_Title 
        from Items i join Items i2 on i.Series_Title = i2.Series_Title
        where i.Item_ID = "S117"

Now, I translated this query in LINQ which goes as

items = _dataContext.Items.AsQueryable();
items = from series in items 
        join members in items on series.Series_Title.ToLower() 
        equals members.Series_Title.ToLower()
        where series.Item_ID.ToLower().Equals(itemId)
        select series;

The last line of this query select series will only retrieve series but not members and I need members also.
I am using MVC3 Razor view where I have to display almost all fields so I am not using select new {....}
Even when I tried to use select new {series, members}, I got this exception -

Cannot implicitly convert type
'System.Linq.IQueryable'
to 'System.LinQ.IQueryable<My.App.models.Items>'
An explicit conversion exist.

Any suggestions would be highly appreciated.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

凉墨 2024-11-16 20:01:21

试试这个：

var items1 = _dataContext.Items.AsQueryable();
var items2 = from series in items1 
        join members in items1 on series.Series_Title
        equals members.Series_Title
        where series.Item_ID== 'S117'
        select series;

Try this:

var items1 = _dataContext.Items.AsQueryable();
var items2 = from series in items1 
        join members in items1 on series.Series_Title
        equals members.Series_Title
        where series.Item_ID== 'S117'
        select series;

回复收藏 0 原文

~没有更多了~