How DbFunctions in Linq expression is evaluated in SQL server

var sent = _context.IsosPassengerNotifications.Any(n => n.Guid == notification.Guid &&
                                           n.DeviceRegistrationId == notification.DeviceRegistrationId &&
   DbFunctions.DiffSeconds(n.CreatedAt, TimeService.UtcNow) < 3);

when you hover over the variable in during debug time you get the SQL command for the linq query

The SQL Equivalent:

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Guid] AS [Guid], 
    [Extent1].[DeviceRegistrationId] AS [DeviceRegistrationId], 
    [Extent1].[DeviceType] AS [DeviceType], 
    [Extent1].[DeviceLanguage] AS [DeviceLanguage], 
    [Extent1].[Title] AS [Title], 
    [Extent1].[CreatedAt] AS [CreatedAt], 
    FROM [dbo].[Notifications] AS [Extent1]
    WHERE (([Extent1].[Guid] = '2019-08-16/356889/977010') OR (([Extent1].[Guid] IS NULL) AND ('2019-08-16/356889/977010' IS NULL))) AND
   (([Extent1].[DeviceRegistrationId] = '01_SsH8BmeM:APA91bGdX01Uvo24qtjayPwePB3L6AWpoqTaGkG0K8SoPOAYSJa1oVzLWKcO9cXEyz8-OetoSSJQPa36upmt38U7cpy-NvfWNTDRWUab_2Vu_abtGfJGLgxIXd-Vy_YJSxvoeH_nbQuQ') OR (([Extent1].[DeviceRegistrationId] IS NULL) AND ('01_SsH8BmeM:APA91bGdX01Uvo24qtjayPwePB3L6AWpoqTaGkG0K8SoPOAYSJa1oVzLWKcO9cXEyz8-OetoSSJQPa36upmt38U7cpy-NvfWNTDRWUab_2Vu_abtGfJGLgxIXd-Vy_YJSxvoeH_nbQuQ' IS NULL))) AND
    ((DATEDIFF (second, [Extent1].[CreatedAt], '2019-09-05 10:40:28.727')) < 3)

So how does DBFunctions.DiffSeconds work. It simply converts to a DATEDIFF in SQL server.

So how does the DATEDIFF work?

DATEDIFF simply gets the second parameter and substracts it from the first parameter.
Here you might get a positive or negative value depending on the parameters passed. To examine the subtraction result you could query the database as below

SELECT DATEDIFF (second, [Extent1].[CreatedAt], '2019-09-05 10:20:28.727')
    FROM [dbo].[Notifications] AS [Extent1]
    WHERE (([Extent1].[Guid] = '2019-08-16/356889/977010') OR (([Extent1].[IsosGuid] IS NULL) AND ('2019-08-16/356889/977010' IS NULL))) AND
   (([Extent1].[DeviceRegistrationId] = '01_SsH8BmeM:APA91bGdX01Uvo24qtjayPwePB3L6AWpoqTaGkG0K8SoPOAYSJa1oVzLWKcO9cXEyz8-OetoSSJQPa36upmt38U7cpy-NvfWNTDRWUab_2Vu_abtGfJGLgxIXd-Vy_YJSxvoeH_nbQuQ') OR (([Extent1].[DeviceRegistrationId] IS NULL) AND ('01_SsH8BmeM:APA91bGdX01Uvo24qtjayPwePB3L6AWpoqTaGkG0K8SoPOAYSJa1oVzLWKcO9cXEyz8-OetoSSJQPa36upmt38U7cpy-NvfWNTDRWUab_2Vu_abtGfJGLgxIXd-Vy_YJSxvoeH_nbQuQ' IS NULL)))


Or else simply try the following query and try changing the parameter values

select DATEDIFF (second, '2019-09-05 10:20:28.727', '2019-09-05 10:21:28.727')

Querying Database tables using Linq

Join two tables. Check for boolean values in columns. Select multiple columns 

var eventFBQuestions = (from FQ in db.FeedbackQuestions join FT in db.FeedbackTypes on FQ.FeedbackTypeId equals FT.Id where (!FQ.Archived && !FT.Archived && FT.Name == "Event") select new { FQ.Id, FQ.Question }).ToList();

Get the count

var countForOne = (from Fb in db.Feedbacks where (Fb.FeedbackQuestionId == question.Id && Fb.FeedbackScore == 1) select Fb.Id).Count();


Join more than two tables. Alter the select columns by concatenating the select fields

            var sessions = (from s in db.Sessions
                        join t in db.Tracks on s.TrackId equals t.Id
                        join e in db.Events on t.EventId equals e.Id
                        where (!s.Archived && !t.Archived && !e.Archived)
                        select new {s.Id, Name = t.Name + " - " +  s.Name }).ToList();

same thing into a dictionary

            var sessions = (from s in db.Sessions
                        join t in db.Tracks on s.TrackId equals t.Id
                        join e in db.Events on t.EventId equals e.Id
                        where (!s.Archived && !t.Archived && !e.Archived)
                        select new {ID=s.Id.ToString(), NAME = t.Name + " - " +  s.Name }).ToDictionary(dic => dic.ID, dic => dic.NAME);

Unable to cast object of type 'System.Data.Entity.Infrastructure.DbQuery`1[System.Int32]' to type 'System.IConvertible'

 Following is a sample linq query which might cause the problem:

int id = Convert.ToInt32(from ts in db.TShirtSizes where ts.Size == "S" select ts.Id);

You get an error as :

Unable to cast object of type 'System.Data.Entity.Infrastructure.DbQuery`1[System.Int32]' to type 'System.IConvertible'

What happens is that, in the initial query you get a list of results. If you need to get a single value, do it by changing the code as shown below

int id = Convert.ToInt32((from ts in db.TShirtSizes where ts.Size == "S" select ts.Id).Single());

Use of Linq when mapping between objects

The class which data is loaded from the database. Notice data is loaded through Entity Framework

    public class UserType : EntityBase
    {
        [DisplayName("User Type")]
        [Required]
        [Remote("IsUserTypeExist", "Validation", ErrorMessage = "user type already exist! ")]
        public string UserTypeName { get; set; }

        public virtual List<User> users { get; set; }
    }

The Data Transfer Object which is used in the system

    public class UserTypeDTO
    {
        public int Id { get; set; }
        public string UserTypeName { get; set; }
        public bool Archived { get; set; }
    }

Mapping inside the method

        public List<UserTypeDTO> GetUserTypes(AppContext db)
        {
            return db.UserTypes.Select(u => new UserTypeDTO()
            {
                Id = u.Id,
                UserTypeName = u.UserTypeName,
                Archived = u.Archived
            }
            ).ToList();
        }