SolvedPrivate

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

Posted on 2014-02-18
18
66 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 69

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 69

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
 
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 69

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 69

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 69

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 69

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
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.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now