Thread: LINQ in MVC5

    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    151
    Rep Power
    19

    LINQ in MVC5


    I need a bit of clarification on how retrieval of data really works in MVC 5 and how i can use LINQ to specify the exact data i want to select.


    Lets say I've got a database partTimeCalendar with a table called reservation. The reservation table looks like this:
    Code:
    id, title, start, end
    in the below abbreviated code snippet db is being set to a new instance of partTimeCalendarEntities.

    Does this query the table, select the data and store it in db for later reference?
    or
    Is that a pointer to the query that will select the data form the table?
    or
    Am i wrong on all accounts

    Code:
    public class reservationsController : Controller
        {
            private partTimeCalendarEntities db = new partTimeCalendarEntities();
    
    //some generic methods blah blah
    
    // GET JSON Calendar view
            public ActionResult calendarJson(string date)
            {
                var calendarObject = db.reservations;
    Also i need to specify a time frame to select so i'm not querying the whole database every time the page is loaded. I tried to do this with LINQ but was unable to specify any columns.

    I guess what i'm saying is i don't know what i need to know to understand what i don't know. I found lots of LINQ articles but they were all referencing lists and nothing really touched MVC.

    Also, if there is a way to just run a query and not have to use LINQ i'm all ears. In coldfusion i would be able to just:

    Code:
    <cfquery name="myQuery" datasource="example">
    SELECT *
    FROM reservation
    WHERE MONTH(start) = #currentMonth#
    </cfquery>
    Thanks for any advice to point me in the right direction.
  2. #2
  3. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,436
    Rep Power
    9645
    Originally Posted by dsfx
    Does this query the table, select the data and store it in db for later reference?
    or
    Is that a pointer to the query that will select the data form the table?
    or
    Am i wrong on all accounts
    db isn't really a "pointer", and you shouldn't call it as such to avoid confusion with people who think you mean a literal C pointer, but otherwise yes (so the second one). LINQ with databases typically does not execute queries until you try to use their results. Even if you build a query with the various methods available, it doesn't execute until later.

    Originally Posted by dsfx
    Also i need to specify a time frame to select so i'm not querying the whole database every time the page is loaded. I tried to do this with LINQ but was unable to specify any columns.
    What code did you try? Are you using the LINQ keywords (from/where/select/etc.) or the methods (like .Where and .Select)?

    With keywords it should go something like
    csharp Code:
    var results = from res in db.reservations
    where res.start.Month = currentMonth // currentMonth is a variable you have
    select res;


    If you Json()ed that then the query would not happen until Json.NET started serializing the data.

    Originally Posted by dsfx
    Also, if there is a way to just run a query and not have to use LINQ i'm all ears.
    Heresy! LINQ is awesome and I wish other programming languages (coughphp) had it. But yes, you can execute queries manually.

    Can I try to convince you to give it another chance? It has the occasional limitation, but for most things it's really powerful.
    Last edited by requinix; July 25th, 2017 at 11:59 AM.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    151
    Rep Power
    19
    Thank you for straightening that out for me. I want to give LINQ a chance, i'm just frustrated so far it feels like there is this layer of abstraction above the HTML/SQL in .NET and it muddies the waters. I come from an Angular/Coldfusion background and it just felt like i was one layer closer to the code. But again it could just be frustration from learning haha.

    Anyways I see where I went wrong with my LINQ statement, it was a stupid mistake. But when i plug the statement you gave me it underlines the Month in res.start.Month. Note it does state that i may be missing a directive or assembly. I did a look up on the requirements and i'm sure i've got everything!

    Here is a list of what i'm using:
    Code:
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Entity;
    using System.Linq;
    using System.Net;
    using System.Web;
    using System.Web.Mvc;
    using partTimeCalendar;
    using System.Web.Script.Serialization;
    using Newtonsoft;
    using Newtonsoft.Json;
    using Newtonsoft.Json.Converters;
    I think i have it all in there, i don't know what i'm missing or doing wrong.

    Here is the problem for reference:
    Code:
    var results = from res in db.reservations
                                  where res.start.Month = myDate.Month // currentMonth is a variable you have
                                  select res;
  6. #4
  7. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,436
    Rep Power
    9645
    That assumes the "start" in db.reservations is defined as a DateTime. Is it something else? It should be, to match the date/datetime you are using (I assume?) in the database table.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    151
    Rep Power
    19
    Originally Posted by requinix
    That assumes the "start" in db.reservations is defined as a DateTime. Is it something else? It should be, to match the date/datetime you are using (I assume?) in the database table.
    You are correct start has a datatype of datetime.

    The reservation.ca file looks like this:
    Code:
    namespace partTimeCalendar
    {
        using System;
        using System.Collections.Generic;
        
        public partial class reservation
        {
            public int Id { get; set; }
            public Nullable<System.DateTime> start { get; set; }
            public Nullable<System.DateTime> end { get; set; }
            public string title { get; set; }
        }
    }
    Last edited by dsfx; July 25th, 2017 at 03:23 PM.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    151
    Rep Power
    19
    upon further research it was because the fields were nullable. I removed teh null value aceptance, rebuilt the project files and that error is gone. Weird.

    https://stackoverflow.com/questions/...time-object-wh
  12. #7
  13. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,436
    Rep Power
    9645
    See, Nullable<System.DateTime> is not actually DateTime. That means it doesn't have a .Month property, and you'd have to adjust the LINQ query to fit.

    However, I would think that start (and maybe end) should not allow null values in the first place, so fixing that is probably the best solution.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    151
    Rep Power
    19
    ugh, so i walked away after i thought i fixed it and now i'm getting a new error with the statement.

    Code:
    if (!string.IsNullOrEmpty(date))
                {
                    DateTime myDate = DateTime.Now;
                    var results = from res in calendarObject
                                  where res.start.Month = 7 // currentMonth is a variable you have
                                  select res;
                }
    I get an error saying "Property of indexer 'DateTime.Month' cannot be assigned to -- it is read only" From what i'm reading this is caused by not having a set option on the method(proper terminology?). As you can see here i clearly do, i don't know what else would cause this to happen.
    Code:
    public partial class reservation
        {
            public int Id { get; set; }
            public System.DateTime start { get; set; }
            public System.DateTime end { get; set; }
            public string title { get; set; }
        }
  16. #9
  17. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,436
    Rep Power
    9645
    Think about what is happening there. Do you think it is supposed to be an assignment? If not then what should it be?

    Perhaps [I made a mistake in my post while thinking about SQL syntax and wrote one = when it should have been two ==s]?
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    151
    Rep Power
    19
    OMG Really?!?!?! I wouldn't have thought in a million years it would try to assign a value in a Where clause.

    Thank you for pointing that out!

    Code:
    if (!string.IsNullOrEmpty(date))
                {
                    DateTime myDate = DateTime.Now;
                    var results = from res in calendarObject
                                  where res.start.Month == 7 // currentMonth is a variable you have
                                  select res;
                }

IMN logo majestic logo threadwatch logo seochat tools logo