Raw SQL query with Entity Framework does not work when calling SELECT of model's field

Question

I cannot run the query due to a strange error that occurs whenever I am trying to use "SELECT" construction

Error is:

The data reader is incompatible with the specified 'BDtest.Ticket'. The member of type 'Id' does not have a corresponding column of the same name in the data reader.

My model is:

public class Departure
{
    public int Id { get; set; }
    public string From { get; set; }
    public string To { get; set; }
    public bool Ended { get; set; }
    public int MaximumTickets { get; set; }

    public List<Ticket> Tickets { get; set; }
}

public class Ticket
{
    public int Id { get; set; }
    public string Passenger { get; set; }
    public double Price { get; set; }
    public string Type { get; set; }

    public int DepartureId { get; set; }
    public Departure Departure { get; set; }
}

The error query code:

var cross = ctx.Database.SqlQuery<Ticket>("SELECT Passenger FROM Tickets WHERE Id=5").ToList();

foreach (var item in cross)
{
    richTextBox1.Text = item.ToString();
}

But this query is working:

var cross = ctx.Database.SqlQuery<Ticket>("SELECT * FROM Tickets WHERE Id=5").ToList();

I tried to do this, but it doesn't work either:

var cross = ctx.Database.SqlQuery("SELECT Passenger FROM dbo.Tickets WHERE Id=5").ToList();

The query that doesn't work is only selecting the [Passenger] column, I think it at minimum needs the primary key column included in the result set. — Crowcoder
@Crowcoder, im trying to use this: var cross = ctx.Tickets.SqlQuery("SELECT Id FROM dbo.Tickets WHERE Id=5"); But error changed: The data reader is incompatible with the specified "BDtest.Ticket". The member of type "Passenger" does not have a matching column of the same name in the data reader. " — inerby466
What is your reluctance to just query all the columns that match the properties in class Ticket? — Crowcoder
I am actually have to use a much more difficult query that references the same error. Here is this query: var cross = ctx.Tickets.SqlQuery("SELECT Id,Passenger,Price,[Sitting],[recumbent],[Staying] FROM dbo.Tickets PIVOT (SUM (Price) for Type in ([Sitting],[recumbent],[Staying])) AS test_pivot").ToList(); — inerby466

Nishan Nishan · Accepted Answer · 2021-04-17T19:09:53

Try

//Get Passenger of string type
string studentName = ctx.Database.SqlQuery<string>("Select Passenger from Tickets where Id=1").FirstOrDefault();

//or
string studentName = ctx.Database.SqlQuery<string>("Select Passenger from Tickets where Id=@id", new SqlParameter("@id", 1)).FirstOrDefault();

Assuming Id is primary key I don't think you will get multiple results, hence FirstOrDefauilt(), but if you get multiple results you can do,

var ticketList = ctx.Tickets
                        .SqlQuery("Select * from Tickets where Id=1")
                        .ToList<Ticket>();

Raw SQL query with Entity Framework does not work when calling SELECT of model's field

1 Answers