当前位置: 动力学知识库 > 问答 > 编程问答 >

c# - Need a subquery in LINQ

问题描述:

I want to Select all items in this table :

_categoryRepository.Table :

Id Name

1 Birthday

2 Christmas

4 Desktops

6 Notebooks

7 Accessories

9 Cell phones

But, . . I want to exclude any 'Id' from _categoryRepository that match 'EventID' in this table :

_MemberEventRepository.Table

Id MemID EventID

1 1 1

3 1 2

5 1 7

7 4 1

8 4 4

that matches the MemId. So for MemID '1', the results would be :

4 Desktops

6 Notebooks

9 Cell phones

I don't know how to do this in LINQ.

 var eventsList = from c in _categoryRepository.Table

join m in _MemberEventRepository.Table on ?????????????

where (m.MemID == currentCustomer)

orderby c.Name

select new MyActiveEvents { Id = c.Id, Name = c.Name };

This the SQL equivalent :

SELECT [Id] ,[Name]

FROM [Category]

WHERE Id NOT IN

(SELECT EventID FROM [Category] c INNER JOIN [MemberEvent] m ON m.[EventID] = c.Id)

Is this possible in LINQ?

网友答案:

Should be something like:

var categories = db.Categories
                   .Where(c => db.MemberEvents.Count(e => EventID == c.Id) == 0);

Update - using your LINQ-code-fragment:

var eventsList = from c in _categoryRepository.Table
                 where _MemberEventRepository.Table
                     .Count(m => m.EventID == c.id) == 0       
                 orderby c.Name
                 select new MyActiveEvents { Id = c.Id, Name = c.Name }

Instead of Count(...) == 0 it should also be possible to use !Any(...).

网友答案:

Maybe an except would work? I'm not sure which solution will translate into the most efficient sql.

var eventsList = (from c in _categoryRepository.Table
                  orderby c.Name
                  select new MyActiveEvents { Id = c.Id, Name = c.Name })
                 .Except(
                from c in _categoryRepository.Table
                join m in _MemberEventRepository.Table on c.Id equals m.EventID
                where (m.MemID == currentCustomer)         
                select new MyActiveEvents { Id = c.Id, Name = c.Name });
分享给朋友:
您可能感兴趣的文章:
随机阅读: