Querying a Single Item in a List

Question

Problem

I want to show a single pet on my details page. I'm having trouble understanding how to write LINQ(Method Syntax) to pull a single pet/item from the database.

My understanding so far

When working with LINQ, it's shining purpose is to be able to loop through an IEnumerable array or list in the database. As you look in my HomeController, I feel fairly confident that I have the right pieces of code to build what I want MINUS the query.

Request

Using method syntax, what query should I be using to pull a single pet?

HomeController.cs

[HttpGet("pet/{PetId}")]
public IActionResult Detail(Pet singlePet)
{
        List<Pet> Animal = _context.Pets
        //***MISSING QUERY CODE HERE***

        return View("Pet", singlePet);
}

Details.cshtml

@model PetViewModel

<div>
    <h1>Pet Shelter</h1>
    @foreach (Pet creature in Model.Pets)
    {
        <h3>Details about: @creature.Name</h3>
        <div>
            <p>Pet Type: @creature.Type</p>
            <p>Description: @creature.Description</p>
            <p>Skill One:@creature.Skill1</p>
            <p>Skill Two:@creature.Skill2</p>
            <p>Skill Three:@creature.Skill3</p>
        </div>
    }
</div>

PetViewModel.cs

using System.Collections.Generic;

namespace petShelter.Models
{
    public class PetViewModel
    {
        public Pet Animal { get; set; }
        public List<Pet> Pets { get; set; }
        public List<Owner> Owners { get; set; }
        public Owner Owner { get; set; }
    }
}

Pet.cs

using System;
using System.ComponentModel.DataAnnotations;

namespace petShelter.Models
{
    public class Pet
    {
        [Key]
        public int PetId { get; set; }

        [Required]
        public string Name { get; set; }

        [Required]
        public string Type { get; set; }

        [Required]
        public string Description { get; set; }

        public string Skill1 { get; set; }
        public string Skill2 { get; set; }
        public string Skill3 { get; set; }


        public DateTime CreatedAt { get; set; }
        public DateTime UpdatedAt { get; set; }

        public Owner Owner { get; set; }
        public int? OwnerId { get; set; }

    }
}

Answer 1

If you don't care and just want a single result from the database you can do something like this

var singlePet = _context.Pets.First();

If you want the first result that matches an expression you can do something like this

var singlePet = _context.Pets.First( e => e.Id == '1');

If you are expecting ONLY a single result that matches an expression you can do something like this

var singlePet = _context.Pets.Single( e => e.PetId == '1' );

Answer 2

If you want just one, with no filter at all on which, you can use this, it will get you the first on the list:

var aSpecificPet = Pets.FirstOrDefault();

If you need to specify some kind of condition to determine which pet to bring, you can use something like:

var aMoreSpecificPet = Pets.FirstOrDefault(x=>x.Type == "Dog");

Take in mind that whatever you pass as a condition will filter the Pets list, and with FirstOrDefault you will get the first (if there are any) pets that match your criteria.

Also, default value is usually null (in case no pets match your criteria), so its a good idea to always add a line like

if(pet != null){
  your code
}

If you want all pets that match that criteria, use:

var allDogsInPets = Pets.Where(x=>x.Type == "Dog").ToList();

The .ToList() returns a new list with the pets that match your criteria.

Answer 3

Short Version

Use

var singlePet=_context.Pets.Find(someId);

Or

var singlePet=_context.Pets.FirstOrDefault(x=>x.PetId=someId);

Explanation

LINQ queries don't query lists. They're translated by a LINQ provider to whatever the target data storage understands. When querying a database, they're translated to SQL.

List<Pet> Animal = _context.Pets won't compile, which is a very good thing - if it did, it would load the entire table in memory instead of querying it. Filtering will be performed on the client, without the benefit of indexing. Performance will be orders of magnitude worse than a simple SQL query due to wasted IO, wasted RAM and even worse, taking locks on every row in the table when only one was needed. Excessive locks will cause delays for every other request that tries to use the same table.

Pets is a DbSet<T>, not a List<T>. It's not a container, it represents an entity without holding any data itself. It can be used to write queries that translate to SQL.

Assuming PetId is the primary key for Pet, the easiest and fastest way to load a single record is calling DbSet.Find. This method retrieves an object using the supplied primary key values.

This query:

var singlePet=_context.Pets.Find(someId);

translates to

select ... 
from Pets 
where PetID=@id

This will load and cache a Pet instance inside DbContext. After that, every time Find is called with the same PK value during the same request, the cached object will be returned.

The query

var singlePet=_context.Pets.FirstOrDefault(x=>x.PetId=someId);

Will translate to

select top 1 ... 
from Pets 
where PetID=@id

Nothing is cached in this case.