Unable to create a constant value of type x, only primitive types or enumeration types are supported in this context

Question

I'm trying to convert a SQL statement into LINQ.

I have these models:

public class GamesNight
{
    public int id { get; set; }

    [DisplayName("Games Night Official Name")]
    public string EventName { get; set; }

    [DisplayName("Description")]
    public string EventDescription { get; set; }
    [DisplayName("Date and Time")]
    [DataType(DataType.Date)]
    public DateTime DateTime { get; set; }

    public virtual ApplicationUser User { get; set; }

    public bool Active { get; set; }
}

and a GamesNightAttendance, this is more or less linking a user to a games night event.

public class GamesNightAttendance
{
    [Key]
    public int id { get; set; }
    public virtual GamesNight GameNight { get; set; }
    public virtual ApplicationUser UserName { get; set; }
    public bool Attendance { get; set; }
}

so a user hosts a GamesNight then other users will be able to attend the games night via the gamesnightattendancemodel.

The query I have is:

var userID = User.Identity.GetUserId();
var user = db.Users.First(x => x.Id == userID);

var result = from GNS in db.GamesNights
    join GNA in db.GamesNightAttendance on GNS.id equals GNA.id
    where GNS.Active & GNA.UserName == user
    select new UpcomingGNAttendanceViewModel { GamesNight = GNS, Attendance = GNA.Attendance};

I get the exception:

Message = "Unable to create a constant value of type 'GNR.Models.ApplicationUser'. Only primitive types or enumeration types are supported in this context."

Can you show the SQL? Perhaps my [SQL to LINQ recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) would be helpful. Also, where do you get the exception? — NetMage, May 25 '18 at 20:52
This doesn't seem like a linq problem... I think the exception message here says it all, which I suspect is something to do with the code we can't see in your `UpcomingGNAttendanceViewModel`. — pim, May 25 '18 at 20:53
I noticed now , Type of "UserName" is" ApplicationUser" and "user" is i guess of Type "User". If this is the case how are you comparing two different objects? — Sumit raj, May 25 '18 at 21:11
Well in the DB the user_id column for gaamesnight and gamesnightattendance is the application user id — Kilvery house, May 25 '18 at 21:14

score 0 · Answer 1 · answered May 25 '18 at 21:21

Your naming conventions don't help you:

public virtual ApplicationUser UserName { get; set; }

That's not a username, that's a user. That table is also missing a property to actually hold the relationship, as well as the property to hold the GameNight Id. The Attendance property also doesn't make a lot of sense, as there is no reason for you to create a GamesNightAttendance with Attendance = false.

That class should look more like this:

public class GamesNightAttendance
{
    public int Id { get; set; }
    // add missing foreign key
    public int GameNightId { get; set; }
    // add missing foreign key
    public int UserId { get; set; }

    public virtual GamesNight GameNight { get; set; }
    // fix name
    public virtual ApplicationUser User { get; set; }
}

With that model, you can now do this:

var userId = User.Identity.GetUserId();

var result = 
    from gameNight in db.GamesNights
    join attendance in db.GamesNightAttendance on gameNight.Id equals attendance.Id
    where gameNight.Active && attendance.UserId == userId
    select new UpcomingGNAttendanceViewModel 
    { 
        GamesNight = gameNight, 
        Attendance = attendance
    };

Unable to create a constant value of type x, only primitive types or enumeration types are supported in this context

1 Answers1