Using an entity table as model in query

[Sulphy]

Hi,

Ok, despite searching the net I've not found a solution that works for this. I have a LINQ to SQL query which i'm using the 'select new' approach to define my results structure:

Code: Select all

select new MyEntityTableNamehere
 {
.....
}

I get a message when I try to debug saying 'Explicit construction of entity type 'MyEntityTableNamehere' in query is not allowed.

From what I can gather this is due to this table having the table attribute assigned to it in the data context. Is there anyway by inheritance that I can define a model which pickups the shape of the entity table. There are a lot of fields and I don't fancy having to setup and maintain another model that will just mirror the table structure.

Thanks ;o)

[MariiaI]

Please send us a test project with the necessary DDL scripts, so that we are able to investigate your scenario in more details. This will significantly speed up the process of finding the solution for you.
Please also tell us the exact product you are using (LinqConnect or dotConnect for Oracle) and its version.

Looking forward to your reply.

[Sulphy]

Hiya,

I managed to make some progress!

I created a new class that inherited from the main table class. This is working fine, although I now know the next error I was getting was caused by my nested child tables.

The table in question has a CLOB which causes my query to fail when the DISTINCT feature is used. So in order to get around this I'm doing a 'select new' but I can't get the child tables populated.

Do you have any suggestions around how to hookup the results of a child table when doing 'select new' e.g.

Code: Select all

select (MainTable) new MainTableInherited 
 {
   Name = item.Name,
   Ex1 = item.Ex1
   ChildTableItems = item.ChildTableItems   //I've tried various ways but it either doesn't get populated or a message is returned stating 'specified method not available'
}

public class MainTableInherited : MainTable
{

}

As a side note if I remove the distinct and select new it all binds fine and the child tables are populated. So I'm assuming it's just something I'm missing entirely in the 'select new' block.

[Sulphy]

A number of hours later and I've found a solution, not sure if it's the most efficient but it works. I've basically split out the operations. One which pulls back to data on the first round based on the main model. Then I do another operation make the result set distinct.

Code: Select all

                
//Make initial call for dataset (includes all rows from table)

                var getResult = (from u in _dbRo.MainTable
                join t in _dbRo.ChildTable on u.ID equals t.PARENT_ID

                where (
                      u.VERSION == "Y" &&
                      u.OPEN == "Y" &&

                select u).ToList();


//Take list from first call and make distinct.

    return getResult.Distinct().ToList();