CODEWITHSUNDEEP
c#linqlinq-to-sqllinq-to-entitieslinq-to-objects
Laziale
Laziale

Reputation: 8225

Linq select a value based on where condition from other table

I have this query:

 var result = (from game in db.Games
                    join gameevent in db.Events
                        on game.GameId equals gameevent.GameId into events
                    from _event in events
                    join _targetObjects in db.TargetObjects
                        on _event.TargetObject equals _targetObjects.TargetObjectId into targetss
                    where game.userId == userId
                    select new ProfileViewModel
                    {
                        record = events.Where(s => s.TargetObjectId == _event.TargetObject && _event.EventType == 35).Select(/* Here I want to select a value from targetss, field called TargetName */).ToList()
                    }).First();

As you can see, I want to get value based on where clause from other table. Is that possible in the select new part?

I want to get the name of the targetObject based on the targetObjectId which matches the targetObjectId in events table and also the event type should be 35.

Upvotes: 1

Views: 2166

Answers (1)

Gabor
Gabor

Reputation: 3246

If the query starts to become a too complex one then it is worth splitting it into parts.

In my example below I use the extension method syntax of LINQ instead of query keywords just because it is easier for me to use.

// First we collect the relevant games.
var games =
    db
        .Games
        .Where(game => game.UserId == userId);

// Then we collect the events of the collected games that have the specified event type.
var events = 
    db
        .Events
        .Join(
            games,
            gameEvent => gameEvent.GameId,
            game => game.GameId,
            (gameEvent, game) => gameEvent
        )
        .Where(gameEvent => gameEvent.EventType == 35);

// Then we collect the target objects based on the collected events.
var targetObjects =
    db
        .TargetObjects
        .Join(
            events,
            targetObject => targetObject.TargetObjectId,
            gameEvent => gameEvent.TargetObjectId,
            (targetObject, gameEvent) => targetObject
        );

// Last we select the target name from the collected target objects.
var records =
    targetObjects
        .Select(targetObject => targetObject.TargetName)
        .ToList(); // The query will be executed at this point.

If this is not what you are looking for, please clarify what data the ProfileViewModel should have exactly and from which set should it be selected as I am not very familiar with this syntax.

Upvotes: 1

Related Questions