SolvedPrivate

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

Posted on 2014-02-18
18
69 Views
Last Modified: 2016-02-10
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
Comment
Question by:allanau20
  • 12
  • 6
18 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 39867022
shouldn't it be:
SqlFunctions.DateAdd("yyyy", u.JoinedYear,  -1900)
0
 
LVL 5

Author Comment

by:allanau20
ID: 39867938
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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 39868047
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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 5

Author Comment

by:allanau20
ID: 39868106
Thanks Eric; same error.
I'll try to mock up some test data for you later today...
0
 
LVL 5

Author Comment

by:allanau20
ID: 39868418
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
 
LVL 5

Author Comment

by:allanau20
ID: 39868427
or is there another way to determine the Age if you've the month ((int)) and year (int)? Thanks!
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 39868524
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
 
LVL 5

Author Comment

by:allanau20
ID: 39868623
didn't mean to confuse you; yes, I'm hitting a database.
In LINQ can you call the method "CalculateAgeCorrect"?
0
 
LVL 5

Author Comment

by:allanau20
ID: 39868625
I thought it would be easier for us if the example was a List. Sorry.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 39868685
the example I pasted is working. try it.
0
 
LVL 5

Author Comment

by:allanau20
ID: 39868717
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
 
LVL 5

Author Comment

by:allanau20
ID: 39868720
Thanks in advance for your help!
0
 
LVL 5

Author Comment

by:allanau20
ID: 39868742
didn't see your earlier post; I will try it now.
0
 
LVL 5

Author Comment

by:allanau20
ID: 39868795
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
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 39869089
remove the ? on your property declaration. Your calculation requires values. You cannot support null
0
 
LVL 5

Author Comment

by:allanau20
ID: 39869103
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
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 500 total points
ID: 39869154
u.JoinedYear is not a date.

SqlFunctions.DateAdd("yyyy", -1900, new datetime(u.JoinedYear, u.JoinedMonth, 1))
0
 
LVL 5

Author Closing Comment

by:allanau20
ID: 39869180
Thanks for your help Eric!
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used.

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question