Search by string method in ASP.NET Core Web API-CodePudding

I'm building an ASP.NET Core searching Web API which should return a list of videos who has the same searched QR code (the QR code is a foreign key).

This is the video model:

    public class Video
    {
        [Key]
        public int VideoId { get; set; }
        public string Exercice { get; set; }
        public string Titre { get; set; }
        public int Sexe { get; set; }
        public int Categorie { get; set; }
        public int Level { get; set; }
        public string FilePath { get; set; } 
        public DateTime DateUpload { get; set; } = DateTime.Now;

        [ForeignKey ("Machine")]
        public string Machine_Qr { get; set; }

        public Machine machine { get; set; }
        public Coache Coache { get; set; }
    }

the machine class:

 public class Machine
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public string Machine_Qr { get; set; }
        public string Machine_Name { get; set; }

        public string ImagePath { get; set; }
        public DateTime InsertedOn = DateTime.Now;
        public List<Video> Videos { get; set; }
    }

And this is the search controller:

[HttpGet("{Qr}")]
public async Task<IEnumerable<Video>> Search(string qr)
{
    IEnumerable<Video> query = _context.videos.Where(e => e.Machine_Qr == qr);

    if ((query != null))
        return query;
    else
        return Enumerable.Empty<Video>().ToList();
}

I tested it and I got an empty list every time.

CodePudding user response：

In order to execute Linq to SQL with Entity Framework you need to update your query like this

[HttpGet("{Qr}")]
 public async Task<IEnumerable<Video>> Search(string qr)
 {
IEnumerable<Video> query = await _context.videos.Where(e => e.Machine_Qr == qr).ToListAsync();

if ((query != null))
    return query;
else
    return Enumerable.Empty<Video>().ToList();

}

by adding .ToListAsync() it will execute your query transforming into SQL and returning the result

CodePudding user response：

Usually the Foreign Key should be int for performance reason and the Machine will look like follow:

public class Machine
{
    [Key]
    public int MachineId { get; set; }
    public string Machine_Qr { get; set; }

    // Another fields
}

public class Video
{
    [Key]
    public int VideoId { get; set; }
    public string Exercice { get; set; }
    public string Titre { get; set; }
    public int Sexe { get; set; }
    public int Categorie { get; set; }
    public int Level { get; set; }
    public string FilePath { get; set; } 
    public DateTime DateUpload { get; set; } = DateTime.Now;

    [ForeignKey ("Machine")]
    public int MachineRefId { get; set; }

    public Machine machine { get; set; }
}

And then:

[HttpGet("{Qr}")]
public async Task<IEnumerable<Video>> Search(string qr)
{
    IEnumerable<Video> query = _context.Videos.Where(m =>m.Machine.Machine_Qr == qr).ToListAsync();
    return query;
}