CODEWITHSUNDEEP
c#asp.net-mvcentity-frameworkapi-design
Paul Pickins
Paul Pickins

Reputation: 69

How to query Entity Framework database for records between 2 dates, then return that information for display on screen

I have an Entity MVC app with a code-first database. I need to produce a search box to search between 2 dates and return the records between those dates. I will call the method with jQuery/ajax and render the results in a table.

I've tried writing an API, with no success. I am not even sure if this is the correct way to go about it?

namespace Areometrex_Leaflet.Models
{
    [Table ("Flight_Lines")]
    public class Flight_line
    {
        [Key]
        public string Swath_name { get; set; }
        public string Flight_name { get; set; } 
        public string Swath_record { get; set; }
        public string Flight_date { get; set; }
        public decimal Start_lat { get; set; }
        public decimal Start_long { get; set; }
        public decimal End_lat { get; set; }
        public decimal End_long { get; set; }
        public decimal Altitude { get; set; }
        public DateTime Time_start { get; set; }
        public DateTime Time_end { get; set; }
        public string Sensor { get; set; }
    }

    public class FlightLineContext : DbContext
    {
        public DbSet<Flight_line> Flight_Lines { get; set; }
    }
}

This is my model that holds the objects in the database. I need to search the "Flight_date" property, that is held in my DB in this following format as an "nvarchar" :

17/11/2018 11:09:18 PM

My current API looks something like this:

[HttpPost]
public IEnumerable<Flight_line> SearchFlight_Line()
{
    string start, end;

    var rc = RequestContext;
    var data = rc.Url.Request.GetQueryNameValuePairs();
    {
        start = data.FirstOrDefault().Value ?? string.Empty;
        end = data.LastOrDefault().Value ?? string.Empty;
    }
    //db format: 17/11/2018 11:22:56 PM
    var s = DateTime.Parse(start);
    var flightSearch = new List<Flight_line>();

    using (_context)
    {
        var sql = $"SELECT * FROM Flight_Lines WHERE Flight_Date BETWEEN '{start}' AND '{end}'";
        flightSearch = _context.Flight_Lines.SqlQuery(sql).ToList<Flight_line>();
    }
    return flightSearch;
}

Ideally, I want to call this API with jquery/Ajax and return results to be displayed in an MVC view. My guess is that this is dead easy, but I am only learning and I'm running out of ideas. I would have thought this was really simple, but I am struggling to find the answers I am looking for online, which leads me to believe perhaps I am doing it wrong?

Upvotes: 0

Views: 2915

Answers (1)

Matt
Matt

Reputation: 1264

First of all, don't save dates as string in your database, you will just have problems later on.

Instead of:

public string Flight_date { get; set; }

Set it up as DateTime:

public DateTime Flight_date { get; set; }

As far as the query for searching flights go, you can try this. This will return a list of "Flight_line" objects which you can then return wherever you need.

DateTime start = DateTime.Now;
DateTime end = DateTime.Now.AddDays(7);

var flights = _context.Flight_line.Where(f => f.Flight_date >= start && f.Flight_date <= end).ToList();

Upvotes: 4

Related Questions