CODEWITHSUNDEEP
c#linq
Kyver
Kyver

Reputation: 37

Retrieving a single column from LINQ joins

I have a few database tables that look like this (could not include the original, so made this up: DB Schema

Here is a sample of what the data could be: Example

The idea is that a Fighter can be a ranger which can have a weapon named "Fire Bow " which has a spell type of "Bow Fire". The user can then select which "Bow Fire" spell they want to use at any time.

Also, a magician can be a wizard that can wield a "Frozen Wand" weapon that has a spell type of "Wand Ice". The user can select which "Wand Ice" spell they they want to use at any time.

Suppose I want to know all unique magic damage types from selected classes. For instance, what magic damage types can a healer and a fighter use? In T-SQL, I would do this:

Select distinct Mag.name from Class C
join SubClass S on C.ID = S.ClassID
join SubClassWeapon W on S.ID = W.SubClassID
join MagicDamageType Mag on W.MagicDamageTypeID = Mag.ID

In the example above, this would give me 2 results (given Fighter and Magician): "Bow Fire" and "Wand Ice".

I'm trying to get this result using LINQ. I am able to retrieve the selected list of classes and I tried this:

ClassList.Select(s => s.SubClasses.Select(s => s.SubClassWeapons.Select(s => s.MagicDamageType.Name))).ToList();

I got some funky nested string list result. Is there any way to do this in LINQ without using foreach loops? I'm just looking for a list of strings with the MagicDamageType names that come from the query.

Thanks!

Upvotes: 1

Views: 81

Answers (1)

Aydin
Aydin

Reputation: 15334

You're looking for SelectMany

ClassList.SelectMany(s => s.SubClasses)
         .SelectMany(s => s.SubClassWeapons)
         .Select(s => s.MagicDamageType.Name).ToList();

Here's an example independent of yours

void Main()
{
    Blog myBlog = new Blog();

    myBlog.Posts
          .SelectMany(post => post.Comments)
          .Select(comment => comment.Id)
          .ToList()
          .ForEach(Console.WriteLine);
}

public class Blog
{
    public Blog()
    {
        Posts = new List<Post>
        {
            new Post(),
            new Post(),
            new Post(),
        };
    }

    public List<Post> Posts { get; set; }
}

public class Post
{
    public Post()
    {
        Comments = new List<Comment>
        {
            new Comment(),
            new Comment(),
        };
    }
    public List<Comment> Comments { get; set; }
}

public class Comment
{
    public Comment()
    {
        this.Id = Guid.NewGuid().ToString("n");
    }
    public string Id { get; set; }

    public override string ToString()
    {
        return this.Id;
    }
}

Upvotes: 1

Related Questions