Deferred Loading with exclusions

[Zero-G.]

Hey

I try to explain, what I do in my project:
First, I get all my customers out of the database. - By Clicking one of these customers the deferred loading comes to action and gets all the orders assocated to the customer.
The problem is, that I have some customers with over 100 of orders during the last 7 years. - Is there a possibility to get only the orders of the last two years by default? - LoadWith Option is not the way, I search, because, then all data of all customers is loaded and this would be an amazing overhead. - THX

[AndreyR]

Use the DataLoadOptions.AssocateWith method.
http://msdn.microsoft.com/en-us/library ... tions.aspx
It can limit the amount of loaded child entities for every loaded master entity depending on the given condition.

[Zero-G.]

Hey

No, this is not, what I am looking for.
Because, I can't change DataLoad Options after the first query is done. - But I have to do so, because the user should be able to change this behaivor during work.
So, the "filter" has to be set, before loading the data.
So, would it be a way, that I fire a LinQ Query for the association by code to overwrite the result?
THx

[AndreyR]

In this case AssociateWith is inappropriate - it cannot be changed at DataContext lifetime.
So, the solution is to write your queries with necessary Where clauses or to use AssociateWith and throw out and recreate the context for users.

[Zero-G.]

Hey

THX for your advise.
I can't get the following working:

Code: Select all

Dim Daten = From Query In myDataContext.Data
Dim relData = From Query in myDataContext.relData Where Query.Value > 10
Daten.Data = relData

How can I add data from a query to a associated data from an other query?

THX

[AndreyR]

I don't think it's possible in LINQ to SQL.
I recommend you to obtain the full list of detail records and then filter this list.
In the other case, there can arise problems with the fact that you have two subsets of rows, which intersect,
and any update will cause a lot of conflict exceptions.
That's why Microsoft have limited the number of AssociateWith calls to one per context.

[Zero-G.]

Hey
Playing around for weeks now and at the end I found a working solution. - Maybe other users are also interested in it.
I try to explain the whole ting once again:
So, I have a customer table, which is associated with the offers table. The Database itself exists over 7 years now and some of our customers have many old offers. The highest count of offers has a customer with 1100 offers (active and finished)
OK - So, when using LinQ, and try to use the deffered loading on the offers table, all offers (active and finished) get loaded from the DB and this uses up to 15 seconds (not acceptable). - So, what I asked here and tried to get better results, was to get only offers which are active OR finished. - But there I have to work with associate with method. - This is also not right for me, because I work with project lifetime DataContext. And now I found a solution for me, how I can load only the data, which I want.
The problem is, that when you use code like this:

Code: Select all

Dim offers = From Query in DataContext.Offers Where Query.isActive = 1

offers became System.LinQ.IQueryable(of Offers)
What means that

Code: Select all

Dim customer = (From Query in DataContext.Customers Wher Query.ID = 1).SingleOrDefault
If Not customer Is Nothing Then
   customer.offer = offers
End If

Will not work, because customer.offer requires a System.Data.LinQ.EntitySet(of Offers)
So, the solution is to get the System.LinQ.IQueryable(of...) into a System.Data.LinQ.EntitySet(of...)
The code is

Code: Select all

Private Function ToEntitySet(Of T As Class)(ByVal source As IEnumerable(Of T)) As Data.Linq.EntitySet(Of T)
        Dim [set] As New Data.Linq.EntitySet(Of T)()
        [set].AddRange(source)
        Return ([set])
    End Function

So, you can do now:

Code: Select all

Dim eOffers As System.Data.LinQ.EntitySet(of Offers) = Nothing
Dim offers = From Query in DataContext.Offers Where Query.isActive = 1
eOffers = ToEntitySet(offers)
Dim customer = (From Query in DataContext.Customers Wher Query.ID = 1).SingleOrDefault
If Not customer Is Nothing Then
   customer.offer = eOffers
End If

Now I can set the isActive to the value I need (in my case 0 or 1) and then, I only get the results I need and the faster speed is perfect.

So, I hope I could help anyone out there with this.
Have a nice weekend

[AndreyR]

Thank you for sharing your knowledge.