SolvedPrivate

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

Posted on 2014-02-18
18
70 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

Independent Software Vendors: 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

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

730 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