CODEWITHSUNDEEP
c#asp.netentity-frameworkrazor-pages
J. Edmond
J. Edmond

Reputation: 41

Select column from another table based on Id using Entity Framework

i have the following classes :

public class Blog {
    public int Id {get; set;}
    public String Name {get; set;}
    ...
    ...
    public int CatId {get;set;}
}

public class BlogCategory{
    public int Id {get; set;}
    public String Name {get; set;}
    public virtual Blogs {get; set;}
}

now i have view model in my razor page :

public BlogViewModel{
    public int Id {get; set;}
    public string Name {get; set;}
    ..
    ..
    public string CategoryName {get; set;}
}

i'm trying to select the blog and to contain it's category name: my Query :

Blogs = await _context.Blogs
    .Select(b => new BlogViewModel()
    {
        Id = b.Id,
        Name = b.Name,        
        //CategoryName = 
    })
    .ToListAsync();

how i can select the Category name from BlogCategory table based on the CatId i have ?

one way is to add 

public virtual Category BlogCat {get; set;}

to the Blog class, then to use Include but i don't want to use this method as i only want the Category Name not the full object.

any help please ?

The Solution:

Blogs = await _context.Blogs
        .Select(b => new BlogViewModel()
        {
            Id = b.Id,
            Name = b.Name,        
            CategoryName = _context.BlogCategory
                        .Where(c => c.Id == b.CatId)
                        .Select(c => c.Name)
                        .SingleOrDefault()
        })
        .ToListAsync();

Upvotes: 0

Views: 3779

Answers (2)

J. Edmond
J. Edmond

Reputation: 41

The Solution:

Blogs = await _context.Blogs
        .Select(b => new BlogViewModel()
        {
            Id = b.Id,
            Name = b.Name,        
            CategoryName = _context.BlogCategory
                        .Where(c => c.Id == b.CatId)
                        .Select(c => c.Name)
                        .SingleOrDefault()
        })
        .ToListAsync();

Upvotes: 1

Felix
Felix

Reputation: 31

    var blogModels = ( from b in _context.Blogs 
     join c in _context.BlogCategories 
     on b.CatId equals c.Id
     select new BlogViewModel()
      {
       Id = b.Id,
       Name = b.Name,        
       CategoryName = c.Name
        }).ToList();

Upvotes: 1

Related Questions