Two issues

Hi,

I ran into two issues with Lightspeed 3.0 (latest nightly version):


First lets start with a bug:
I am trying to query a table that has 3 user defined types (enumerators) as an anonymous (or directly into a Dto class) by doing this:

var query = from bla in uow.Blas select new { bla.Enum1, bla.Enum2, bla.Enum3 };

When you then try to loop through the query using a foreach, it throws an exception. If needed I can look at the exact exception and possibly give a sample project. Note, the database I am querying against is MSSQL. The enumerators themselves are stored as bytes in the database.

Secondly a "how would you solve this":
I need to retrieve millions of records and send them to a remote service. The problem that I am running into is that a single unit of work keeps each entity it creates in a cache (or at least thats how it looks).

Initially I looked into this structure:

using(var uow = context.CreateUnitOfWork())
{
var query = from bla in uow.blaas where bla.whatever>4 select bla;
var count = query.Count();

for(var pos = 0; pos < count; pos += 5000)
{
var arr = query.OrderBy(b => b.Id).Skip(pos).Take(5000).Select(b => b.AsDto()).ToArray();
service.SendToMe(arr);
}
}

While the above code works (assuming my 'pseudo code' doesnt have errors), the problem lies in the "Select(b => b.AsDto())" part, Lightspeed will create the bla-entity on which it will run the AsDto method. Even though the entity itself is never used, a reference within Lightspeed is kept to this entity. The end result was several gigabytes of memory used before 2-3m records where send.

Now I did find a way around this issue (doing the count in its own uow and then creating a uow inside of the for-loop instead of around the for-loop). But its far from "nice code".

While my new found solution works, its far from ideal. The code could be better if I could actually create the Dto's inside the select without using the AsDto (but due to the earlier mentioned Enum bug, thats not possible). It gets around the entity-creation (and as such the caching 'error' associated with it).

Even then though I would wonder if there's a faster way of retrieving these amounts from the database, sending them in packets of 5-10k records and then disgarding them. The best way I could think of would be a SqlDataReader, would this get me faster record-retrieval speed?

Regards,

Jerremy

Hi Jerremy,

In regards to #1 - if you could let give us more detail on this that would help us in tracking this down - a repro sample is always very useful for this :)

For #2, you should look at using UnitOfWork.Project which returns an IDataReader for handling scenarios where you dont want to have entities hydrated but want to work over the raw data such as your scenario above, but you would need to write some custom mapping code rather than using the .AsDto extension methods.

Jeremy

To expand a bit on Jeremy's comments:

1. I think we currently only support enums stored as integers in the database.  But as Jeremy says, information about the exception message or a repro case is always useful.

2. In addition to using Project to get an IDataReader, you can use a LINQ Select, but you'll need to explicitly load properties because LINQ can't look inside the AsDto method:

query.Select(b => new BlahDto {
  Prop1 = b.Prop1,
  Prop2 = b.Prop2  // etc.
});

This will give you a sequence of BlahDto objects which saves you working down at the data reader level -- but internally it is executing a projection rather than loading an entity so it should result in no entities being cached in the UOW.

I've included a test project. Test case #0 will succeed, but both test case #1 and test case #2 will fail. Even though test case #1 uses an integer enumerator!  As said, this test was run against MSSQL (not sure if it matters).

I wanted to use the Select but the enum-bug was getting in my way. Is there any info of the performance difference between a raw IDataReader and a Select into either a Dto or anonymous type?

And as far as I know, an IDataReader is a forward-only DataReader. How would this compare to, lets say, this code:

foreach(var test in uow.Tests.Select(x => new { x.Whatever }) { }

Will the above foreach first get all the data, or act like a IDataReader and retrieve per row?

A LINQ Select currently gets all the rows (i.e. it assembles all the results into a list rather than yielding them one at a time as they come off the data reader).  (It doesn't get all the columns.)

I don't think this need be an issue for you, though, because you are building batches anyway.  So you can use the Skip and Take operators to load only the rows required for the current batch.

Re your test case, can you provide us with the CREATE TABLE statements for your database please?  Thanks!

Well I created the table using LightSpeed with the exception of later changing the Id to Identity (forgot that during creation). But the script is this:

USE [QuoteExport]
GO

/****** Object:  Table [dbo].[TestEntities]    Script Date: 03/11/2010 08:20:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TestEntities](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [TestEnum1] [int] NOT NULL,
    [TestEnum2] [tinyint] NOT NULL,
 CONSTRAINT [PK__TestEnti__3214EC0721B6055D] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[TestEntities] ADD  CONSTRAINT [DF__TestEntit__TestE__239E4DCF]  DEFAULT ((0)) FOR [TestEnum1]
GO

ALTER TABLE [dbo].[TestEntities] ADD  CONSTRAINT [DF__TestEntit__TestE__24927208]  DEFAULT ((0)) FOR [TestEnum2]
GO

Why on earth did LightSpeed add constraints?  Not that it should matter.

This is a bug with our handling of enums in a LINQ projection.  LINQ projections are materialised in a slightly different way to entities, which is why your case #0 worked but your cases #1 and #2 didn't -- the bug was on the LINQ projection path, not the entity materialisation path.

I've committed a fix for this and it will be included in nightly builds dated 13 Mar 2010 and above.  Please let us know if you still see problems.  Thanks for reporting this issue and for the detailed repro case!