SolvedPrivate

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

Posted on 2014-02-18
18
71 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

724 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