Lookup queries the database everytime

[XXXX]

Hi all,
  I have a situation where I'm loading data into the database for each record I have to lookup ID's that are related to that record.
What I have noticed is that the table holding the ID's gets queried for every record loaded, there are much fewer IDs than records to be loaded so I expected the cache mechanics to kick in so there would be less queries to the lookup table.
Am I experiencing the same as described last in this thread http://www.telerik.com/community/forums/orm/general-discussions/l2-cache-not-working.aspx, that since there are more than 500 records/items in the lookup table it's not cached ?.

Since I only need the ID but not the OA object could I change the function that gets the ID so it would be more efficent ?
it looks something like this

 public bool TryFindID(short type, string externalID, out int ID)
 {
      Lookup dim= this.Lookup.FirstOrDefault(d => string.Equals(d.Source_Id, externalID) && short.Equals(d.type, type));
     if (dim != null)
     {
         ID = dim.Id;
         return true;
     }
     else
     {
         ID = int.MinValue;
         return false;
     }
 }

Thanks in advance.

[Jan Blessenohl]

Hi Björn,
We have to find exactly this query result, with the same parameters for type and externalID in the cache, how often is that possible?

If you do not need the object you can also say:

this.Lookup.Where(x=>x.Source_Id == externalID && x.Type == type).Select(x=>x.ID).FirstOrDefault()

This will only give you the int values back.

Greetings,
Jan Blessenohl
the Telerik team

[XXXX]

Thanks for the reply.

The actual ratio between records and IDs can vary, but is expected to be more than 8.000 to 1

Sadly the "this.Lookup.Where(....)" happens to return 0 when not found but 0 is a valid ID unless we take precaution to prevent the use 0 as ID. We are currently testing to load into memory "lite" version of the Lookup only with those 3 fields needed for the lookup.
Hoping that the cost of having the Lookup values in memory is outweighted by the umproved speed.

[Jan Blessenohl]

Hi Björn,

You can also work with a

this.Lookup.Where(x=>x.Source_Id == externalID && x.Type == type).Select(x=>x.ID).Cast<int?>().FirstOrDefault()

if you want to see a null.

All the best,
Jan Blessenohl
the Telerik team

[XXXX]

Thanks for that suggestion.
But when I add .Cast(int?) to the query I get a runtime error

The compiled query cache contains an entry for this query, but it's result converter is not producing the required type : 'System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.
Parameter name: targetType
Actual value was System.Nullable`1[[System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]], mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089.ArgumentOutOfRangeException

I'm using ORM 2011.3.112
The fix is instead of .Cast(int?) I have .Select(x=> (int?) x.ID).FirstOrDefault();

I did some tests, in a loop I executed context.TryFindID about 10.000 times with the same parameters.
It resulted in 10.000 requests sent to the database (SQLserver Activity monitor), I did 3 variations of the TryFindID function: the original and 2 that cast the id to int?.
The few simple tests I did, did not show any noticable difference in performance.

Thanks for your help.

[Thomas]

Hello Björn,

the working way to do this is to use

this.Lookup.Where(x=>x.Source_Id == externalID && x.Type == type).Select(x=>(int?)x.ID).FirstOrDefault()

I can reproduce the error that you reported, but I fear we will be able to fix this for the upcoming Q1 2011 release, but only afterwards.

Greetings,
Thomas
the Telerik team