Retrieving all values as strings from SQL Server

Question

I'm currently using EF Code-First, and I'd like to SELECT data from many tables in the database. This query is a customizable query, and hence I can't predict what kind of data I will be retrieving.

At first, when I tried running ctx.Database.SqlQuery<string>(sql, param), I ran into an exception when facing a DateTime value. I'd like to do this without casting it on the server side.

Does anybody have any idea how I can go about doing it? It can be in LINQ, LINQ-SQL, or purely SQL--so long as it gets the job done! Thanks guys...

Ladislav Mrnka · Accepted Answer · 2012-04-23 08:59:59Z

1

You will not get it. Linq-to-entities will not make transformation to list of strings. Your best chance is executing normal queries and do conversion and transformation your application.

Argument that you don't know which columns user selects just means you need more dynamic solution - Linq-to-entities is not a good tool for you (except if you try to use Dynamic Linq or build expression trees manually). Use ESQL or SQL directly.

answered Apr 23, 2012 at 8:59

Ladislav Mrnka

365k60 gold badges666 silver badges673 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

matt Over a year ago

Not the finest solution, I know, but I followed what you did and added convert(nvarchar(500), ) to every value, and it worked wonders. Thanks! :)

Mahmoud Gamal · Accepted Answer · 2012-04-23 05:53:56Z

0

When selecting data from many tables, use anonymous types to encapsulate the properties(fields) you want to select from these tables into a new entity, something like:

var query = _db.Categories.Join(
_db.Products,
c => c.CategoryId,
p => p.CategoryId,
(category, product) =>
   new
   {
       ProductName = product.Name,
       CategoryName = category.Name,
       ExpiryDate = product.ExpiryDate
   });

answered Apr 23, 2012 at 5:53

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

1 Comment

matt Over a year ago

I don't really know what I'm getting from the query; it's up to the user to decide. This means that at times it can be SELECT [Product].[Product][Name], and at other times, SELECT [Product].[ExpiryDate]. I'd just like to get a List<string> from the query. Thanks

Zaheer Ahmed · Accepted Answer · 2012-04-23 05:57:08Z

0

You can achieve string values by casting your data fields to string in this way:

var query = _db.Categories.Join(
_db.Products,
c => c.CategoryId,
p => p.CategoryId,
(category, product) =>
   new
   {
       ProductName = product.Name.toString(),
       CategoryName = category.Name.toString(),
       ExpiryDate = product.ExpiryDate.toString()
   });

answered Apr 23, 2012 at 5:57

Zaheer Ahmed

28.7k12 gold badges77 silver badges113 bronze badges

1 Comment

matt Over a year ago

I don't really know what I'm getting from the query; it's up to the user to decide. This means that at times it can be SELECT [Product].[Product][Name], and at other times, SELECT [Product].[ExpiryDate]. I'd just like to get a List<string> from the query. Thanks

Collectives™ on Stack Overflow

Retrieving all values as strings from SQL Server

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related