Database Querying issue in Entity Framework. Getting null each time

Question

My problem is, that every time when I try querying the database with LinQ methods, I get either Null when using SingleOrDefault(), or an InvalidOperationException when using Single().

Here's my code.

var currentUser = UserManager.FindById(User.Identity.GetUserId());

var post = ApplicationDbContext.Posts
                .Include(c => c.Author)
                .SingleOrDefault(c => c.Author.Id == currentUser.Id);

if (post == null)
    return View(new CertainPost());

I'm suspicious if it ain't wrong that I've used the .Include method on author and in the same time I used the author value to query the DB. If that's the issue, how should I write this properly? Can I in some way use SingleOrDefault() method later in my code, when Author will be loaded? I've been doing it like this, but I find this way very messy

//Such a mess
var posts = ApplicationDbContext.Posts
                .Include(c => c.Author)
                .ToList();

foreach(var item in posts)
{
    if(item.Author.Id == currentUser.Id)
        var post = ApplicationDbContext.Posts.SingleOrDefault(c=>c.Id==item.Id)
}

So, what code should I write to compromise on usefulness and optimization?

Try if this works...var post = ApplicationDbContext.Posts.Include(c => c.Author) .ToList().SingleOrDefault(c => c.Author.Id == currentUser.Id); — Pikoh
– Pikoh, Commented Jul 26, 2017 at 16:21
Nah, still the same. I feel like i cant use .Include(c => c.Author) and immedeatly ask for it in c => c.Author.Id == currentUser.Id in the same expression. I guess it should load authors 1st, and then I should ask for author Id. But the issue is i don't know how to write it, because if I won't use SingleOrDefault, Single or ToList(), It returns IQueryable<> object, instead of Post object. — Jan Paweł Niepsuj
– Jan Paweł Niepsuj, Commented Jul 26, 2017 at 16:24
If you have a referenced entity Author, then you should also have an AuthorId property in your Post class - no? Just use that: .SingleOrDefault(p => p.AuthorId == currentUser.Id); — marc_s
– marc_s, Commented Jul 26, 2017 at 16:27
Nah, I aint got authorId class, I used object binding, So i've got property public ApplicationUser Author {get;set} instead of public int AuthorId {get;set} — Jan Paweł Niepsuj
– Jan Paweł Niepsuj, Commented Jul 26, 2017 at 16:31
Nothing is wrong with your query. Include is unrelated to what you put as criteria because the query is translated to SQL. May be simply there are no posts for the user you are querying. — Ivan Stoev
– Ivan Stoev, Commented Jul 26, 2017 at 16:33

Chris Pratt · Accepted Answer · 2017-07-26 17:17:34Z

2

The problem is likely that you have multiple posts for that author. The only meaningful logical difference between the non-working code and the working but "messy" code is that, in the working code, you're selecting by an item id.

The difference between Single and SingleOrDefault is only that when the first fails, it raises an exception, while the second will simply return null. With either, there's actually two scenarios that will make them fail:

There are no matching items
There are more than one matching items

Since you're sure that the first is not the situation (you've confirmed there is a post), then the second is the issue.

Long and short, you need to either provide some other differentiating detail to SingleOrDefault such that it can match one and only one post, or you need to just use something like Where and return all matching posts:

var post = ApplicationDbContext.Posts
    .Include(c => c.Author)
    .SingleOrDefault(c => c.Author.Id == currentUser.Id && c.Id == postId);

Or

var posts = ApplicationDbContext.Posts
    .Include(c => c.Author)
    .Where(c => c.Author.Id == currentUser.Id);

FWIW, you don't need to use Include for any relationships that are part of the query. In the case of Author, it has to be joined in order to figure out if its id equals the current user's id, so it will already be included.

Additionally, it's an unnecessary query to lookup the user when all your need is the id. You already have the id, since you used that to look the user up in the first place. You might have done that because passing User.Identity.GetUserId() directly to your query will raise an exception. However, that only occurs because Entity Framework doesn't know how to translate that method to something it can do in a SQL query. If you pass it just the value, you're fine:

var userId = User.Identity.GetUserId();
var posts = ApplicationDbContext.Posts
    .Where(c => c.Author.Id == userId);

answered Jul 26, 2017 at 17:17

Chris Pratt

240k37 gold badges414 silver badges468 bronze badges

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

3 Comments

Jan Paweł Niepsuj Over a year ago

Actually, posts and authors are in 1 to 1 relationship.

Chris Pratt Over a year ago

That may have been your intention, but I don't think that's actually the situation. If you post your entity classes, we might be able to spot a potential problem.

Jan Paweł Niepsuj Over a year ago

allthough i have only one post in database for Now. I'm not home Now so I can't show it

Collectives™ on Stack Overflow

Database Querying issue in Entity Framework. Getting null each time

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related