Building a query that uses 'select new'

[Sulphy]

Hi,

Thought I'd repost this question now I have a better idea of the problem and how to explain it.

My app has been using the .ExecuteQuery<Persons>(qrySQL) approach to pull back data and I want to move away from this and use LINQ to SQL queries.

I have to use 'select new' as I have some fields which need to be excluded completely from the DB call. In Entity Developer 'Persons' is defined as a table (with a [Table] attribute in the code). It's for this reason that when I'm creating a query I can't do the below in my query:

Code: Select all

select new Persons() {
....
 }

Otherwise I get the message: Explicit construction of entity type 'Persons' in query is not allowed.

I did some searches on google and found an article that mentions inheritance. So I gave this a go and made a little progress but not much.

Code: Select all

public class PersonsView : Persons { }

After creating an inherited table I also added some associations to the child table for navigation purposes. However the navigation properties aka child tables never get populated.

Needless to say I've spent ages trying different tweaks to the LINQ to SQL statement. I decided that I wanted to prove that the table which uses inheritance (PersonsView) could be used by the .ExecuteQuery method:

Code: Select all

myContext.ExecuteQuery<Persons>(qrySQL);

vs.

Code: Select all

myContext.ExecuteQuery<PersonsView>(qrySQL);

Here I think I've proved my attempt at table inheritance has failed. I get errors which point to the linked tables/navigations stating that they couldn't be found, despite the correct associations being in place.

In short, the problem I'm trying to get over is to be able to select a specific set of fields while holding on to the navigation properties so my child tables will be populated. For the moment I think the problem resides on the fact I'm not using the actual table but one which is using inheritance.

I'd really appreciate some examples that might help sort this out :O)

[Shalex]

I have to use 'select new' as I have some fields which need to be excluded completely from the DB call.

You can either implement Table Splitting or remove unnecessary fields from your entity in the model.
Table Splitting can be easily (no manual editing of *.lqml) made in Entity Developer: drag&drop particular (unnecessary or with big data) properties from table on the diagram surface and choose Table Splitting in the dialog.

After creating an inherited table I also added some associations to the child table for navigation purposes. However the navigation properties aka child tables never get populated.

As we understood, you are working with a child entity which is not registered in the data context. In this case, a derived class does not participate in the Lazy Loading and other DataContext features.