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

Question

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?

you should forget about raw SQL queries when you work with Entity Framework — vasily.sib
– vasily.sib, Commented Apr 15, 2019 at 5:40
What problem are you facing? You are stating what you want to do, but I don't see any error or code for webrequests, that would use this code. — Marco
– Marco, Commented Apr 15, 2019 at 6:51
Don't save dates as strings. Once you fix that you can use a simple LINQ query, eg .Where(line=>line.Flight_Date >= startDate && line.Flight_Date < endDate) — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Apr 15, 2019 at 7:34
If you want to save dates, just use whatever date-only type is available in your database. SQL Server for example has date. This will still be mapped to DateTime in C# but EF will take care of generating the correct query — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Apr 15, 2019 at 7:35

Matt · Accepted Answer · 2019-04-15 07:31:54Z

4

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();

edited Apr 15, 2019 at 7:31

answered Apr 15, 2019 at 7:26

Matt

1,2622 gold badges20 silver badges36 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related