Linq to Entities and concatenated properties

Question

Hi,

Does anyone know if its possible to create a new property on an existing Entity Type which is based on 2 other properties concatenated together?

E.g. My Person Entity Type has these fields "ID", "Forename", "Surname", "DOB"

I want to create a new field called "Fullname" which is

Forenames + " " + Surname

So i end up with "ID", "Forename", "Surname", "DOB", "Fullname".

I know i can do this using Linq programmatically i.e.

var results = from p in db.People
select new { 
ID = p.ID, 
Forename = p.Forename, 
Surname = p.Surname, 
DOB = p.DOB,
Fullname = p.Forename+ " " + p.Surname
};

Then calling something like

var resultsAfterConcat = from q in results 
where q.Fullname.Contains(value)
select q;

However i'd really like to use Linq to Entities to do this work for me at the Conceptual Model level.

Answer 1

hey,

Thanks for your answer, I tried that first as it seemed the best solution. Linq doesn't like this though and will throw the following type of error when you try and use the new property in a query:

The specified type member 'FullName' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

That's why i don't really want to do it programmatically. The way i suggested works and produces a pretty neat SQL statement. However I want to be able to do this at an XML Level by editing the "edmx" file. That way it should be ok to use the new property with linq.

I guess i was wondering if there's some way you can do it using the mapping stuff available in linq to entities.

Thanks

Dave

Answer 2

Not yet, but maybe soon. First, note that your suggested query will not work at all in LINQ to Entities, with or without the property, because, at present, it doesn't support Contains. The new version of the Entity Framework in .NET 4.0, however, is supposed to support custom methods in LINQ to Entities queries. You can see a video about this from PDC. Essentially, you have to write the custom method twice; once in code, and once on your database (e.g., in a calculated field). See the video for more information.

Answer 3

Craig,

Sarted watching the video, then realised it's over an hour long, so will have to watch it when i have more time. Just to let you know though.. Contains seems to be working ok for me, here's the SQL that's generated by Linq to Entities:

SELECT 
1 AS [C1], 
[Extent1].[PeopleID] AS [PeopleID], 
[Extent1].[Forenames] AS [Forenames], 
[Extent1].[Surname] AS [Surname]
FROM [dbo].[People] AS [Extent1]
WHERE (CHARINDEX(N'Dave', [Extent1].[Forenames] + N' ' + [Extent1].[Surname])) > 0

It seems to work a treat. Using CHARINDEX to workout if the Concatinated field contains the entered text which is the above case was "Dave".

Thanks Dave

Answer 4

The reason Contains "works" for you is because you're calling String.Contains, and not IEnumerable.Contains, as Craig thought.