LINQ Subset ORM

Hi All,

I have a table with a bunch of columns.  To Date I've been using Linq2SQL for my ORM, but as my next phase of R&D, I'm interested in tuning the performance.  

My concern right now is that in order to use LINQ to SQL it seems like it needs to pull the all of the columns across the wire . . . which to me is absurdley inefficient.  

The error I'm currently getting is:
Explicit construction of entity type Img in query is not allowed.

The bigger picture I guess is how do I retreive a subset of my table using LINQ (or some other ORM) ?  

What's the best practice for doing this taking into account that I need to be able to access this from silverlight?

Thanks in advanced.
Public Function Load() As List(Of Img) Implements Iimg.Load

        Dim i As New CabClassesDataContext(ConfigurationManager.ConnectionStrings("MyDb").ToString())

        Dim q = From c In i.Images Select New Img With {.Row = i.Row, .Col = i.Col, .OffsetX = i.OffsetX, .OffsetY = i.OffsetY, .RowSpan = i.RowSpan, .ColSpan = i.ColSpan
}


        Return q.ToList()


    End Function

Open in new window

LVL 42
Kyle AbrahamsSenior .Net DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fernando SotoRetiredCommented:
Hi ged325;

Is Img a table in the ORM model? If not please post the class Img.

Fernando
0
Kyle AbrahamsSenior .Net DeveloperAuthor Commented:
img is a Table.

Has a bunch of columns but the one's here are the one's I'm interested in grabbing.
0
Fernando SotoRetiredCommented:
Hi ged325;

The error message is stating that you can't use a table from the ORM model in that way. What you will need to do is create a local class and use it in its place. For example first create a class as such:

Public Class MyImg

    Public Row As Integer
    Public Col As Integer
    Public OffsetX As Integer
    Public OffsetY As Integer
    Public RowSpan As Integer
    Public ColSpan As Integer

End Class      

then you can do the following:

Dim q = From c In i.Images
        Select New MyImg With _
        { _
            .Row = i.Row, _
            .Col = i.Col, _
            .OffsetX = i.OffsetX, _
            .OffsetY = i.OffsetY, _
            .RowSpan = i.RowSpan, _
            .ColSpan = i.ColSpan _
        }


Fernando
0
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Kyle AbrahamsSenior .Net DeveloperAuthor Commented:
Hi Fernando,

Thanks for the quick replies.  

So essentially what you're saying is that the service will always do a select * from the table so to speak.

Create a new class client side and then use LINQ from there?  What happens to the rest of the data?  Having a hard time where the split between the server and the client comes in.

Essentially what I'm looking to do is have the client request the data that it needs via the service, service returns data, and client processes it.  Where do these classes belong (or do they need to be in both places?)

Do I need to create a new class for every combo?  Again it seems really ineffecient (especially when it comes to joins and what not.)  What's the best way to get data to my silverlight client?


0
Fernando SotoRetiredCommented:
Hi ged325;

To your question, "So essentially what you're saying is that the service will always do a select * from the table so to speak.", No, lets say that the database table has ten columns, col1, col2, ..., col10 and you query the table but only want to return 3 columns 1, 4 and 10. What you will do in the select clause is just select those columns, for example:

Dim q = From c In i.MyTable _
             Select { _
                 c.col1,  _
                 c.col4, _
                 c.col10, _
             }

To the question, "Create a new class client side and then use LINQ from there?", It sounds like you have a web service. In a case like this the client side method call to the service, the server runs the query and then returns the data back to the client. So the query is run on the server side and just returns the results.

To the question, "What happens to the rest of the data?", Just return the needed fields from the query as shown above. So you are just sending what is needed and nothing else.

To the question, "Essentially what I'm looking to do is have the client request the data that it needs via the service, service returns data, and client processes it.  Where do these classes belong (or do they need to be in both places?)", Well the class to hold the data return from the query will need to be in both and this is part of the service and when the client subscribes to the service it will be part of the interface on the client side.

To the question, "Do I need to create a new class for every combo?", You could create one class with all the files that could be returned from the service and just fill what is need for each query or create one for each return type.

Fernando
0
Kyle AbrahamsSenior .Net DeveloperAuthor Commented:
I understand everything except this last piece:

To the question, "Do I need to create a new class for every combo?", You could create one class with all the files that could be returned from the service and just fill what is need for each query or create one for each return type.

So let's say I have a class with all the members of the table.  I call a subset of the load with 3 out of the 10 columns.  The client side instatiates the class (with all members) but will only have data for those 3 members?  

What happens to the type saftey at this point?  What if I try to access a member that wasn't part of the select?  (eg, cols 1,2,4 chosen, and on my client I reference 6).  Not that this should be the case, but just looking for any gotchyas and what's the best way to go about designing all of this.


Thanks for all the very detailed answers.
0
Fernando SotoRetiredCommented:
To the question, "So let's say I have a class with all the members of the table.  I call a subset of the load with 3 out of the 10 columns.  The client side instatiates the class (with all members) but will only have data for those 3 members?", That would be correct but remember even though you only query and retrieved three columns the default values are passed as well. You would be better off creating a different class for each return type and just pass the needed info so that less info is passed over the wire.

To your question, "What happens to the type saftey at this point?", it will be enforced because you have a class that defines all the fields and these files will at least have a default value.

To the question, "What if I try to access a member that wasn't part of the select?", it will return the default value for the type for example null for objects and zero for int's and so on.
0
Kyle AbrahamsSenior .Net DeveloperAuthor Commented:
So going back to:

To the question, "Create a new class client side and then use LINQ from there?", It sounds like you have a web service. In a case like this the client side method call to the service, the server runs the query and then returns the data back to the client. So the query is run on the server side and just returns the results.

To the question, "What happens to the rest of the data?", Just return the needed fields from the query as shown above. So you are just sending what is needed and nothing else.


Even though I only requested the 3 columns I'm still shipping extraneous data (eg: default values) over the wire for other data members that aren't there.  

To your statement:
"You would be better off creating a different class for each return type and just pass the needed info so that less info is passed over the wire.  "

Is this the way it's done in "real world" applications?  Is there always a new Result class built for each service function?  Feels like there should be an easier, more efficient way.  How would you design this if you were going to code an application (a few super sets with relations, but need multiple subset of that information)  

Again, thanks for all the help.  I'm gone for the rest of today, but will be back in the AM.  Looking forward to your response.
0
Fernando SotoRetiredCommented:
To your statement, "Even though I only requested the 3 columns I'm still shipping extraneous data (eg: default values) over the wire for other data members that aren't there.", That is why you should create a data structure that only returns what is needed for each return type in the service that returns a complex data type.

To your question, "Is this the way it's done in "real world" applications?", When returning a complex type yes.

To your question, "Is there always a new Result class built for each service function?" There is always a a structure or class when more then one data type is being returned.

To your statement, "Feels like there should be an easier, more efficient way.", What would be more easier and efficient then sending only what you need in a simple package like a class.

To your question, "How would you design this if you were going to code an application (a few super sets with relations, but need multiple subset of that information) ", Well that would need allot more info but I would always try to separate into a three tier approach possibly using WCF.  
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kyle AbrahamsSenior .Net DeveloperAuthor Commented:
Thanks very much for all the help.  This made a lot of sense and when combining with LINQ to Entities I believe I'll have all the info needed.  Appreciate your help and complete answers for each question.
0
Fernando SotoRetiredCommented:
Not a problem, always glad to help out.  ;=)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.