Entity Framework Database query results in NULL values

Question

I am creating a WPF app using C#, Entity Framework and SQL Server 2008 R2. I am trying to fire the following query.

Select Convert(nvarchar(10),bk.BookingDate,103) as Date, Count(*) 
from Booking bk
where  
    Convert(date,bk.BookingDate) between '2014-10-01' and '2014-10-31'
    and bk.IsDeleted = 0
group by Convert(nvarchar(10),bk.BookingDate,103)
order by 1

This query works if directly fired on SQL Server prompt and fetches results. However when I am trying using Entity Framework I am getting NULL value.

var values = context.Database
    .SqlQuery<KeyValuePair<string, int>>(query)
    .ToList<KeyValuePair<string, int>>();

enter image description here

Is it because I am using a KeyValue pair in the method call?
Is there any alternative of doing this using Entity Framework?
Or should I use old style database connection and command to run this query?

why are you alternating using BookingDate as a string and date? If it's stored as a date in your database, select it and compare it as a date — Kritner
– Kritner, Commented Oct 27, 2014 at 16:27
Just a hint in case you are not aware: EntityFramework works best with LinQ and NOT writing plain Text SQL-Queries. — TGlatzer
– TGlatzer, Commented Oct 27, 2014 at 16:29
@Kritner: in database its datetime. I want count by date. So need to erase the time part — Nilesh Barai
– Nilesh Barai, Commented Oct 27, 2014 at 16:30

CodingGorilla · Accepted Answer · 2014-10-27 16:36:35Z

2

I think this is because you're using the KeyValuePair<>, try creating a simple class like:

public class MyResult
{
    public DateTime Date { get; set; }
    public int TotalCount { get; set; }
}

And add count(*) as TotalCount to your query. And use that as the generic type parameter for your SqlQuery call. A key thing to remember is that EF will look for members on the result type with names that match the columns returned from the query. So as an example, if your query returned a column called "total_count", you would need a property on your class also called "total_count" (with the underscore).

edited Oct 27, 2014 at 16:36

answered Oct 27, 2014 at 16:30

CodingGorilla

19.9k4 gold badges48 silver badges65 bronze badges

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

1 Comment

Nilesh Barai Over a year ago

Its worth a try!! Thanks :)

Collectives™ on Stack Overflow

Entity Framework Database query results in NULL values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related