how to fetch data from database using linq query for relationship 1:N and N:N (between 3 entity) in asp.net mvc EF code first?

Question

i have a linq query for fetch data from 3 table (Post, Category, Tag).

i want show latest posts (10 num) in default page of a blog: post title, post tags name and post category name, post short description. i type this linq query

List<Post> IBlogRepository.PostsForList(int pageNo, int pageSize)
    {
        using (var context = new MJBweblogContext())
        {
            IQueryable posts = from Post in context.Posts
                        where Post.Published == true
                        select new
                        {
                            Post.Title,
                            Post.ShortDescription,
                            Post.Description,
                            Post.MetaData,
                            Post.PostedOn,
                            Post.UrlSlug,
                            Post.Published,
                            Post.Modified,
                            Post.Category,
                            Post.Tags,
                            Post.Category.Name,
                        };
            if (posts != null)
            {
                return posts.OfType<Post>() // i give error if i remove OfType<Post>()
                    .OrderByDescending(p => p.PostedOn)
                    .Skip(pageNo * pageSize)
                    .Take(pageSize)
                    .ToList();
            }
            else
            {
                return null;
            }
        }
    }

but i have two problem:

if i remove OfType() VS2017 tell me:

Error CS0266 Cannot implicitly convert type 'System.Linq.IQueryable' to 'System.Collections.Generic.List'. An explicit conversion exists (are you missing a cast?) MJBweblog C:\Users\m.j.b\source\repos\MJBweblog\MJBweblog\DAL\BlogRepository.cs 44 Active
1. I don't know what code I should write to fetch tags for each post and their category? in the linq query? (i try to show user something like this template of each post in default page)

this is What I want to show for users in default page. (a list of this)

You are projecting an anonymous type in posts. You should define a custom ViewModel which holds all the properties you want to pass to the view and project it. — Rahul Singh
– Rahul Singh, Commented Feb 1, 2018 at 18:58
you are creating a new anonymous object. you can't just cast it to a defined type. create a concrete type from your anonymous one and return that. — Fran
– Fran, Commented Feb 1, 2018 at 18:58
it's also bad practice to return a null object when you are querying. null has a very different connotation than no items. return an empty list instead. — Fran
– Fran, Commented Feb 1, 2018 at 19:01
@Fran The way it is defined, posts variable is never null — Ivan Stoev
– Ivan Stoev, Commented Feb 1, 2018 at 19:43
@IvanStoev True, ef queries always return something. definitely get rid of the if/else statement. And the OP thinks he's returning a null. — Fran
– Fran, Commented Feb 1, 2018 at 19:56

Vidmantas Blazevicius · Accepted Answer · 2018-02-01 19:43:02Z

1

If your Post class is an entity class then you don't need to create another anonymous type with select. Below Linq chain should work for what you are trying to achieve. Also as per the comments - it is not a good practice to return null for a list, the caller can check the Count property to see if the list is empty.

        using (var context = new MJBweblogContext())
        {
            return context.Posts.Where(post => post.Published)
                .OrderByDescending(p => p.PostedOn)
                .Skip(pageNo * pageSize)
                .Take(pageSize)
                .ToList();
        }

answered Feb 1, 2018 at 19:43

Vidmantas Blazevicius

4,8322 gold badges14 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JamieD77 · Accepted Answer · 2018-02-01 19:50:38Z

You can't just skip all of that other stuff?

List<Post> IBlogRepository.PostsForList(int pageNo, int pageSize)
    {
        using (var context = new MJBweblogContext())
        {
            return context.Posts.Where(a => a.Published == true)
                                .OrderByDescending(p => p.PostedOn)
                                .Skip(pageNo * pageSize)
                                .Take(pageSize)
                                .ToList();
        }
    }

if you're not getting your category information, you should be able to use INCLUDE

List<Post> IBlogRepository.PostsForList(int pageNo, int pageSize)
    {
        using (var context = new MJBweblogContext())
        {
            return context.Posts.Include("Category").Where(a => a.Published == true)
                                .OrderByDescending(p => p.PostedOn)
                                .Skip(pageNo * pageSize)
                                .Take(pageSize)
                                .ToList();
        }
    }

Collectives™ on Stack Overflow

how to fetch data from database using linq query for relationship 1:N and N:N (between 3 entity) in asp.net mvc EF code first?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related