• Status: Solved
  • Priority: Medium
  • Security: Private
  • Views: 76
  • Last Modified:

SqlFunctions.DateAdd("yyyy", u.JoinedYear - 1900, 0)

Hi Experts!

Have this sql that tells me the years the user being a member:
SELECT   datediff(d, dateadd(d, 01, dateadd(m, ISNULL(JoinedMonth,01), dateadd(yyyy, JoinedYear-1900, 0))), getdate())/365 as age
FROM Users u
WHERE JoinedYear IS NOT NULL 

Open in new window

Trying to convert to LINQ to Entity:
 .....select new
   {
   Age = (SqlFunctions.DateDiff("d", SqlFunctions.DateAdd("d", 01, SqlFunctions.DateAdd("m", ((int?)u.JoinedMonth ?? (int?)01), SqlFunctions.DateAdd("yyyy", u.JoinedYear - 1900, 0))), SqlFunctions.GetDate()) / 365) 

Open in new window

The error is:
Argument 3: cannot convert from 'int' to 'System.DateTime?'      
Think it doesn't like this part:
SqlFunctions.DateAdd("yyyy", u.JoinedYear - 1900, 0)

Any ideas?

TIA!
0
allanau20
Asked:
allanau20
  • 12
  • 6
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
shouldn't it be:
SqlFunctions.DateAdd("yyyy", u.JoinedYear,  -1900)
0
 
allanau20Author Commented:
Thanks Eric; but it has the same error and the - (negative) is underlined in blue.
Just to try it out took out the - (negative) and it says that cannot convert int to System.Date.Time for '1900'.
0
 
Éric MoreauSenior .Net ConsultantCommented:
as per http://technet.microsoft.com/en-us/library/ms186819.aspx, the syntax is: DATEADD (datepart , number , date )

SqlFunctions.DateAdd("yyyy", -1900, u.JoinedYear)

and if I was to use it from SQL directly, I would use:
SqlFunctions.DateAdd(year, -1900, u.JoinedYear)
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
allanau20Author Commented:
Thanks Eric; same error.
I'll try to mock up some test data for you later today...
0
 
allanau20Author Commented:
The UserTemp class:
public class UserTemp
{
    public int UserID { get; set; }
    public int? JoinedYear { get; set; }
    public int? JoinedMonth { get; set; }
}

Open in new window


Here's the code that error:
List<UserTemp> listuser = new List<UserTemp>(); 
var UserT = new UserTemp {UserID = 1, JoinedYear = 1980, JoinedMonth = null };    
listuser.Add(UserT);

var query = from u in listuser
            select new
            {
                Age = (SqlFunctions.DateDiff("d", SqlFunctions.DateAdd("d", 01, SqlFunctions.DateAdd("m", ((int?)u.JoinedMonth ?? (int?)01), SqlFunctions.DateAdd("yyyy", -1900, u.JoinedYear))), SqlFunctions.GetDate()) / 365)
            };

Open in new window


Were you able to get the Age? TIA!
0
 
allanau20Author Commented:
or is there another way to determine the Age if you've the month ((int)) and year (int)? Thanks!
0
 
Éric MoreauSenior .Net ConsultantCommented:
I thought you were hitting a database! When you use SqlFunctions, you need to run this LINQ query against a database, not an in-memory list!

check that:

            private void ultraButton1_Click(object sender, EventArgs e)
            {
                  List<UserTemp> listuser = new List<UserTemp>();
                  var UserT = new UserTemp { UserID = 1, JoinedYear = 1980, JoinedMonth = 1 };
                  listuser.Add(UserT);

                  var query = (from u in listuser
                                    select new
                                    {
                                          Age = CalculateAgeCorrect(new DateTime(u.JoinedYear, u.JoinedMonth, 1), DateTime.Today)
                                    }).ToList();
            }

            public int CalculateAgeCorrect(DateTime birthDate, DateTime now)
            {
                  int age = now.Year - birthDate.Year;
                  if (now.Month < birthDate.Month || (now.Month == birthDate.Month && now.Day < birthDate.Day)) age--;
                  return age;
            }
      }

      public class UserTemp
      {
            public int UserID { get; set; }
            public int JoinedYear { get; set; }
            public int JoinedMonth { get; set; }
      }
0
 
allanau20Author Commented:
didn't mean to confuse you; yes, I'm hitting a database.
In LINQ can you call the method "CalculateAgeCorrect"?
0
 
allanau20Author Commented:
I thought it would be easier for us if the example was a List. Sorry.
0
 
Éric MoreauSenior .Net ConsultantCommented:
the example I pasted is working. try it.
0
 
allanau20Author Commented:
Please let me try again:

UserTemp class:
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

public class UserTemp
{
    [Key]
    public int UserTID { get; set; }
    public int? JoinedYear { get; set; }
    public int? JoinedMonth { get; set; }
}

Open in new window

DBContext:
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;

/// <summary>
/// Summary description for ClassName
/// </summary>
public class MyDB : DbContext
{
    public DbSet<UserTemp> UserTemps { get; set; }

    // This runs when the database is created.  
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        // Turns off cascading deletes
        // Cascading deletes causes an issue when a table has more than 1 FK from the same table.
        modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
    }
}

Open in new window

DB Initializer:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;

/// <summary>
/// Summary description for MyDBInitializer
/// </summary>
public class MyDBInitializer : CreateDatabaseIfNotExists<MyDB>
{
    protected override void Seed(MyDB db)
    {
        // UserTemp
        var userT = new List<UserTemp>
        {
            new UserTemp{UserTID = 1, JoinedYear = 1980, JoinedMonth = null }, 
            new UserTemp{UserTID = 2, JoinedYear = 1990, JoinedMonth = null }, 
            new UserTemp{UserTID = 3, JoinedYear = 2000, JoinedMonth = null }, 
            new UserTemp{UserTID = 4, JoinedYear = 2010, JoinedMonth = null } 
        };
        userT.ForEach(t => db.UserTemps.Add(t));
        db.SaveChanges();

    }
}

Open in new window

temp.cshtml page:
@{
    MyDB db = new MyDB();


    var query = from u in db.UserTemps
                select new
                {
                    Age = (System.Data.Objects.SqlClient.SqlFunctions.DateDiff("d", System.Data.Objects.SqlClient.SqlFunctions.DateAdd("d", 01, System.Data.Objects.SqlClient.SqlFunctions.DateAdd("m", ((int?)u.JoinedMonth ?? (int?)01), System.Data.Objects.SqlClient.SqlFunctions.DateAdd("yyyy", -1900, u.JoinedYear))), System.Data.Objects.SqlClient.SqlFunctions.GetDate()) / 365)
                };

    db.Dispose();
}

Open in new window

0
 
allanau20Author Commented:
Thanks in advance for your help!
0
 
allanau20Author Commented:
didn't see your earlier post; I will try it now.
0
 
allanau20Author Commented:
Tried this:
@{
    MyDB db = new MyDB();

var query = (from u in db.UserTemps
                select new
                {
                        Age = UserBLL.CalculateAgeCorrect(new DateTime(u.JoinedYear, u.JoinedMonth, 1), DateTime.Today)
                }).ToList();
   db.Dispose();}

Open in new window


Got this error:
cannot convert from 'int?' to 'int'
0
 
Éric MoreauSenior .Net ConsultantCommented:
remove the ? on your property declaration. Your calculation requires values. You cannot support null
0
 
allanau20Author Commented:
guess it cannot be help.

Tried this and it compiled OK:
Age = UserBLL.CalculateAgeCorrect(new DateTime(u.JoinedYear, u.JoinedMonth, 1), DateTime.Today)

Open in new window

But when you run it we get this:
Message=LINQ to Entities does not recognize the method 'Int32 CalculateAgeCorrect(System.DateTime, System.DateTime)' method, and this method cannot be translated into a store expression
Tried these:
SqlFunctions.DateAdd("yyyy", u.JoinedYear, -1900)
SqlFunctions.DateAdd("yyyy", -1900, u.JoinedYear)

Open in new window

and we get this:

Argument 3: cannot convert from 'int' to 'System.DateTime?'

Any ideas? Thanks!
0
 
Éric MoreauSenior .Net ConsultantCommented:
u.JoinedYear is not a date.

SqlFunctions.DateAdd("yyyy", -1900, new datetime(u.JoinedYear, u.JoinedMonth, 1))
0
 
allanau20Author Commented:
Thanks for your help Eric!
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 12
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now