LINQ converting portions of a character string to a date

Question

I have a stored procedure that is returning a varchar(255) field that contains a folder name, like:  C:\SCC\2012-05-08.

In my c# code my dataworker is filling in a my entity class which has this field defined as string.

List<FoldersEntity> list = (from x in context.FoldersGetProc(fromDate) select new FoldersEntity { nFolder = x.nFolder }).ToList();

list[0].nFolder = "C:\SCC\5/8/2012"

How oh how do I get it to stop converting the last segment to a date?

Answer 1

Ok, after playing around with everything on the c# side of the world, I went back to my stored proc.  This is so bizarre...but after 6 hours, I have a work around...

This code: Declare @folderDate varchar(10)=convert(varchar, @folderDateIN, 23) --2012-05-10

then using this var in a string: Insert @t table(nFileName) Select 'C:\SCC\'+@folderDate -- C:\SCC\2012-05-10 causes LINQ to give: C:\SCC\5/10/2012

BUT if I change the code to this mess:

Declare @d int=datepart(dd, @folderDateIN), @m int=datepart(mm, @folderDateIN), @y int=datepart(yyyy, @folderDateIN)

Declare @folderDate varchar(10)=str(@y,4)+'-'+replace(str(@m,2), ' ', '0')+'-'+replace(str(@d,2), ' ', '0')

-- C:\SCC\2012-05-10 causes LINQ to give: C:\SCC\2012-05-10

Why/how/what causes the messy code to work better than a simple convert statement is beyond me.  If anyone has any answers, I'd sure love to hear it.

               

---

Lee

Answer 2

Hi Leester,

Welcome to MSDN Forum.

I think it may caused by some configuration about your IDE, but I'm not sure. I have wrote a demo and try to repro the issue, but the part of the string doesn't convert to date. Below is the code and the steps I have done.

Test database table and Stored Procedure:

create table dataPath
(
	id int primary key identity,
	[path] varchar(255) not null
)

insert into dataPath (path) values('C:\SCC\2012-05-08')

create procedure getData
as
select id,path from pathTest

2. Add an Entity Model to the project and include the table and the stored procedure in the Entity Model.

3. In the designer, import the stored procedure as the function. The return type of the function is a new complex type, id's type is int and path's type is string.

4. Write code to execute the stored procedure.

class Program
    {
        static void Main(string[] args)
        {
            using (TestEntities context = new TestEntities())
            {
                var query = (from p in context.getData_Result() select p.path).ToList();
                Console.WriteLine(query[0].ToString());
                Console.Read();
            }
        }
    }

The result is

Best Regards

---

Allen Li [MSFT]
MSDN Community Support | Feedback to us

Answer 3

Hi Leester,

I'm writing to check the status of the issue on your side, Would you mind letting me know does the issue has already solved? If you need further assistence, please feel free to let me know. I will be more than happy to be of assistance.

Have a nice day.

---

Allen Li [MSFT]
MSDN Community Support | Feedback to us

Answer 4

Hi Allen,

Sorry for the delay in responding, I've been under the gun to get this app out to the users today.

Thanks for checking this out.  The problem only appears if you are building strings in a stored proc.  If I saved the values to a real table vs. @table & then use another stored proc to just pull the values directly from the table, no conversion takes place.  For this app, I don't need to store these values, except long enough to create the folders on the end-user workstations.

It really comes down to these lines of code in a stored proc:

This code: Declare @folderDate varchar(10)=convert(varchar, @folderDateIN, 23) --2012-05-10

then using this var in a string: Insert @t table(nFileName) Select 'C:\SCC\'+@folderDate -- C:\SCC\2012-05-10 causes LINQ to give: C:\SCC\5/10/2012

create proc testItProc As

Declare @t Table(Line varchar(255))

Declare @folderDate varchar(10)=convert(varchar, @folderDateIN, 23)

Insert @t table(nFileName) Select 'C:\SCC\'+@folderDate

Insert @t table(nFileName) Select 'C:\SCC\'+@folderDate+'\FolderA'

Select Line From @T

go

Then calling testItProc from a LINQ Data Worker. I am using SQL 2008 R2 Dev for SQL, not sure if that makes any difference either, since I don't have SQL 2005 any more & haven't built a SQL 2012 server yet :)

---

Lee

Answer 5

Hi Leester,

I'm not familar with stored procedure, I think I may need more time to find the root cause. Any way, I'm glad to hear that you get a workaround. I mark the workaround as answer, this will help other members who encounters the similar issue. If you feel it is not appropriate, please feel free to unmark it. Thanks for your sharing. :)

Best Regards

---

Allen Li [MSFT]
MSDN Community Support | Feedback to us