How do I extract this LinqToSql data into a POCO object?

Question

Hi folks,

with my Repository classes, I use LinqToSql to retrieve the data from the repository (eg. Sql Server 2008, in my example). I place the result data into a POCO object. Works great :)

Now, if my POCO object has a child property, (which is another POCO object or an IList), i'm trying to figure out a way to populate that data. I'm just not too sure how to do this.

Here's some sample code i have. Please note the last property I'm setting. It compiles, but it's not 'right'. It's not the POCO object instance .. and i'm not sure how to code that last line.

public IQueryable<GameFile> GetGameFiles(bool includeUserIdAccess)
{
    return (from q in Database.Files
            select new Core.GameFile
            {
                CheckedOn = q.CheckedOn.Value,
                FileName = q.FileName,
                GameFileId = q.FileId,
                GameType = (Core.GameType)q.GameTypeId,
                IsActive = q.IsActive,
                LastFilePosition = q.LastFilePosition.Value,
                UniqueName = q.UniqueName,
                UpdatedOn = q.UpdatedOn.Value,

                // Now any children....
                // NOTE: I wish to create a POCO object
                //        that has an int UserId _and_ a string Name.
                UserAccess = includeUserIdAccess ?
                    q.FileUserAccesses.Select(x => x.UserId).ToList() : null
            });
}

Notes:

• Database.Files => The File table.
• Database.FilesUserAccess => the FilesUserAccess table .. which users have access to the GameFiles / Files table.

Update

I've now got a suggestion to extract the children results into their respective POCO classes, but this is what the Visual Studio Debugger is saying the class is :-

Why is it a System.Data.Linq.SqlClient.Implementation.ObjectMaterializer<..>

.Convert<Core.GameFile> and not a List<Core.GameFile> containing the POCO's?

Any suggestions what that is / what I've done wrong?

Update 2:

this is what i've done to extract the children data into their respective poco's..

// Now any children....
UserIdAccess = includeUserIdAccess ? 
    (from x in q.FileUserAccesses
     select x.UserId).ToList() : null,
LogEntries = includeUserIdAccess ? 
    (from x in q.LogEntries
     select new Core.LogEntry
     {
         ClientGuid = x.ClientGuid,
         ClientIpAndPort = x.ClientIpAndPort,
         // ... snip other properties
         Violation = x.Violation
     }).ToList() : null

Answer 1

What is the type of UserIdAccess? How is it not 'right'? Are you getting the 'wrong' data? if so have you checked your database directly to make sure the 'right' data is there?

Answer 2

I think that all you need to do is to put another Linq query in here:

q.FileUserAccesses.Select(x => x.UserId).ToList()

i.e. You want to select data from the FileUserAccess records - which I'm assuming are Linq to SQL classes, so to do this you can have something like:

(from fua in q.FileUserAccesses
select new PocoType
{
   UserID = fua.UserID, 
   Name = fua.User.UserName // Not sure at this point where the name comes from
}).ToList()

That should get you pointed in the right direction at least.