Linq query with nested select(s)

Question

Trying to figure out the right way to express the following SQL in a linq statement and getting nowhere.

select messageid, comment 
from Message 
where isactive = 1 and messageid in 
(
    select messageid 
    from org.Notification 
    where NotificationTypeId = 7 and orguserid in 
    (
        select OrgUserId 
        from org.OrgUser
        where OrgId in 
        (
            select ChildOrgId from OrgRelation
            where ParentOrgId = 10001
        )
    )
    group by messageid
)

This works as-is in SQL Server, but I want to use this query in C# (E-F) code, and I seem to be running around in circles.

uh.. what did you actually mean with running around in circles? i don't see any cyclic reference.. — Bagus Tesa
– Bagus Tesa, Commented Mar 23, 2018 at 6:32
Do these tables have no relation ? If there is a relation, why dont you use joins. If there isnt any relation, why do you need the data from them. Also, if it is getting complex, put that in a stored proc and call it. — Praneet Nadkar
– Praneet Nadkar, Commented Mar 23, 2018 at 6:35
Please include the code that you tried and your thoughts behind it. That will also tell us if you want method syntax or query syntax. — Peter B
– Peter B, Commented Mar 23, 2018 at 9:13
@BagusTesa "running around in circles" - it's an expression. I didn't mean it literally in terms of cyclic references. Sorry for any confusion. — BogeyMan
– BogeyMan, Commented Mar 23, 2018 at 15:30
@PraneetNadkar - yes. There are some relations. I guess I could go down the join path also. I used this example simply because it gives me the data I need (in SQL). — BogeyMan
– BogeyMan, Commented Mar 23, 2018 at 15:32

Harald Coppoolse · Accepted Answer · 2018-03-23 09:04:47Z

Apparently Notification objects have a messageId. Several Notification objects may have the same value of MessageId.

I want the Messages that have an Id that equal one of the MessageId values of a sub-selection of all Notification objects.

First I'll show you a LINQ query that solves your problem, then I'll tell you something about entity framework that would make this kind of LINQ queries easier to understand and maintain.

Direct Solution

(1) Select the notifications of whom the messages should be fetched:

IQueryable<Notification> notificationsToUse = org.Notifications
    .Where(notification => notification.TypeId == 7
          && ....);

This is your inner select. I'm not sure about the relations between Notifications, OrgUsers and OrgRelations. But that is outside this question.

(2) Extract all used MessageIds of these Notifications

IQueryable<int> messageIdsUsedByNotificationsToUse = notificationsToUse
    .Select(notification => notification.MessageId)
    // remove duplicates:
    .Distinct();

(3) Fetch all active messages with an Id in `messageIdsUsedByNotificationsToUse

IQueryable<Message> fetchedActiveMessages = org.Messages
    .Where(message => message.IsActive
        && messageIdsUsedByNotificationsToUse.Contains(message.Id));

(4) You don't want the complete message, you only want the MessageId and the Comment:

var result = fetchedActiveMessages.Select(message => new
{
    MessageId = message.Id,
    Comment = message.Comment,
});

TODO: if desired: make one big LINQ statement.

Until now you haven't accessed the database yet. I only changed the Expression in the IQueryable. Making it one big LINQ statement won't increase performance very much and I doubt whether it would improve readability and maintainability.

Solution using possibilities of entity framework

It seems there is a one-to-many relation between Message and Notification: Every Message has zero or more Notifications, every Notification belongs to exactly one Message, using the foreign key MessageId.

If you stuck to the entity framework code-first conventions, you designed your classes similar to the following. (emphasizing the one-to-many relation):

class Message
{
    public int Id {get; set;}

    // every Message has zero or more Notifications (one-to-many)
    public virtual ICollection<Notification> Notifications {get; set;}

    ... // other properties
}

class Notification
{
    public int Id {get; set;}

    // every Notifications belongs to exactly one Message using foreign key
    public int MessageId {get; set;}
    public virtual Message Message {get; set;}

    ... // other properties
}

class MyDbContext : DbContext
{
    public DbSet<Message> Messages {get; set;}
    public DbSet<Notification> Notifications {get; set;}
}

This is all entity framework needs to know that you planned a one-to-many relation between Messages and Notifications. Entity framework knows which properties you intended to be the primary keys and the foreign keys, and it knows about the relation between the tables.

Sometimes there are good reasons to deviate from the conventions. This needs to be solved using attributes or fluent API.

The important thing is the structure with the virutal ICollection from Message to Notification and the virtual reference back from Notification to the Message that it belongs to.

If you've designed your classes like this, your query will be a piece of cake:

(1) Select the notifications you want to use:

IQueryable<Notification> notificationsToUse = ... same as above

(2) Now you can select the messages belonging to these Notifications directly:

var result = notificationsToUse.Select(notification => notification.Message)

Because every notification belongs to exactly one message, I'm certain there are no duplicates.

Continuing: only the MessageId and the Comment of the active messages

    .Where(message => message.IsActive)
    .Select(message => new
    {
        MessageId = message.Id,
        Comment = message.Comment,
    });

I wasn't sure about the relations between Notifications, OrgUsers and OrgRelations. If you design your classes such that they represent a proper one-to-many or many-to-many relation, then even expression (1) will be much simpler.

vahdet · Accepted Answer · 2019-05-17 08:54:34Z

2

You can break down your query into 3 different parts as shown below -

Filter out the OrgUserIds first

var filteredOrgUserIds = dc.OrgUsers.Where(u => dc.OrgRelations.Where(o 
    =>o.ParentOrgId == 10001).Select(d => 
    d.ChildOrgId).ToList().Contains(u.OrgId))
    .Select(uid => uid.OrgUserId)
    .Distinct()
    .ToList();

Using the filteredOrgUserIds to filter out the notifications.

var filteredNotifications = dc.Notifications.Where(n => 
     n.NotificationTypeId == 7 && filteredOrgUserIds.Contains(n.OrgUserId))
     .Select(m => m.messageid)
     .Distinct()
     .ToList();

Lastly filter out the messages by looking at the filteredNotifications.

    m.isactive == 1 && filteredNotifications.Contains(m.messageid))
    .Select(m => new { message = m.messageid, comment = m.comment })
    .Distinct()
    .ToList();

This should work.Please try this once from your end and let us know if this helps.

edited May 17, 2019 at 8:54

vahdet

6,78910 gold badges62 silver badges116 bronze badges

answered Mar 23, 2018 at 10:28

Aritra B

1,7466 gold badges29 silver badges47 bronze badges

3 Comments

BogeyMan Over a year ago

This is the approach I have adopted (at the moment). it seems to work pretty well. However, in the case(es) where lists of Ids gets large, performance seems to suffer greatly.

BogeyMan Over a year ago

Further comment on this answer. I worked for me in this case, but I ended up making some minor edits. In the example above, every sequence calls the .ToList(). In my case I removed that in order to defer the final execution.

Aritra B Over a year ago

Hello Sir, if you are still having performance issues, then why not go for a stored procedure? If you need further help on calling SPs in EF6 let us know.

Marco Barbero · Accepted Answer · 2018-03-23 07:08:22Z

0

If you need to use the IN clause you shoud use the Contains method.

answered Mar 23, 2018 at 7:08

Marco Barbero

1,5381 gold badge14 silver badges22 bronze badges

Comments

NetMage · Accepted Answer · 2018-03-23 18:07:16Z

From my Recipe for converting SQL to LINQ to SQL:

Translate subselects as separately declared variables.
Translate each clause in LINQ clause order, translating monadic and aggregate operators (DISTINCT, TOP, MIN, MAX etc) into functions applied to the whole LINQ query.
SELECT fields must be replaced with select new { ... } creating an anonymous object with all the desired fields or expressions.
Translate IN to .Contains() and NOT IN to !...Contains().

Noting that your SQL query is using GROUP BY when it should use DISTINCT, and then applying the recipe rules:

var childOrgs = from or in OrgRelation where or.ParentOrgId == 1001 select or.ChildOrgId;
var orgUsers = from ou in OrgUser where childOrgs.Contains(ou.OrgId) select ou.OrgUserId;
var notificationMessages = (from n in Notification
                            where n.NotificationTypeId == 7 && orgUsers.Contains(n.orguserid)
                            select n.messageid).Distinct();
var ans = from m in Message
          where m.isactive == 1 && notificationMessages.Contains(m.messageid)
          select new { m.messageid, m.comment };

Collectives™ on Stack Overflow

Linq query with nested select(s)

4 Answers 4

Direct Solution

Solution using possibilities of entity framework

Comments

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Direct Solution

Solution using possibilities of entity framework

Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related