Linq UNION query to select two elements

Question

I want to select 2 elements from my database table using LINQ query and I saw an example which use UNION I don't have much experience but I think that maybe this is what I need but I get an error which I can not fix and I'm not sure if it's fixable anyway. So here is my query:

    IList<String> materialTypes = ((from tom in context.MaterialTypes
                                   where tom.IsActive == true
                                   select tom.Name)
                                   .Union(from tom in context.MaterialTypes
                                   where tom.IsActive == true
                                   select (tom.ID))).ToList();

Which as it seems is complaining about trying to use UNION on IQueryable with IEnumarebale. I tried to fix that by adding ToString() like this - (tom.ID).ToString which led to cleaning the error underline in Visual-Studio-2010 but in runtime I get:

{"LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression."}

Ty, Leron.

Why aren't you just selecting `new { tom.Name, tom.ID }` instead of using `Union`? — Jon Skeet, Feb 22 '13 at 10:49
I want to use the data as my `DataSource` if I try with it gives an error that can not convert from anonymous type to string.If I add `ToString()` right before `.ToList()` I get another convertion error - this time from `char` to `String` and the `Union` is solution that I saw for these kind of situations. Is there another way? — Leron, Feb 22 '13 at 10:54

score 50 · Accepted Answer · edited May 23 '17 at 11:52

50

EDIT:

Ok I found why the int.ToString() in LINQtoEF fails, please read this post: Problem with converting int to string in Linq to entities

This works on my side :

        List<string> materialTypes = (from u in result.Users
                                      select u.LastName)
                       .Union(from u in result.Users
                               select SqlFunctions.StringConvert((double) u.UserId)).ToList();

On yours it should be like this:

    IList<String> materialTypes = ((from tom in context.MaterialTypes
                                       where tom.IsActive == true
                                       select tom.Name)
                                       .Union(from tom in context.MaterialTypes
                                       where tom.IsActive == true
                                       select SqlFunctions.StringConvert((double)tom.ID))).ToList();

Thanks, i've learnt something today :)

edited May 23 '17 at 11:52

Community

1
1

answered Feb 22 '13 at 10:55

Tom

944
1
14
26

1

Again - `LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression.` The mistake from my original post was an inner exception. This is what I get when I try your way. I think the same as with brackets. – Leron Feb 22 '13 at 11:00
Lol, I'll go to lunch and test it after. If it works gonna accept your answer – Leron Feb 22 '13 at 11:10
Wow I would never gonna think of that. Thanks a lot. It works for me too! – Leron Feb 22 '13 at 11:52

Linq UNION query to select two elements

1 Answers1

Linked