Problem getting same result from DataContext.Translate

This question is a follow up to this question: How to create a list from two values

Consider this code:

class MainClass() 
{
   string MainKey {get;set;}
   string MainName {get;set;}
   IEnumerable<SmallObject> MainList {get;set} 
}

class SmallObject() 
{
   string SmallKey {get;set} 
} 

and:

var mainQuery = (from v from DataContext.myTable
                 select v);

var myQuery = (from v in mainQuery
               开发者_高级运维select new MainClass()
               {
                  MainKey = v.Field1,
                  MainName = v.Field2,
                  MainList = new []
                  {
                     new SmallObject { SmallKey = v.Field3 },
                     new SmallObject { SmallKey = v.Field4 },
                  }
                });


var result1 = myQuery.ToList();

//Changing datatypes for optimization reasons in SQLServer2000
var cmd = DataContext.GetCommand(myQuery);
foreach (System.Data.Common.DbParameter param in cmd.Parameters)
{
  // nvarchar -> varchar
  // decimal -> numeric
}
var result2 = DataContext.Translate<MainClass>(cmd.ExecuteReader()).ToList();

result1.MainList is OK

result2.MainList is null

The original query was very slow running on SQLServer2000, and I got it fixed when changing datatypes (Linq uses nvarchar and decimal, as my database use varchar and numeric)

So I want result2 to be the same as result1, but that doesn't happen when doing a DataContext.Translate like this.

Any thoughts of getting the same result here?

I've also tryed anonymous types, like this:

IEnumerable<object> MainList {get;set;}
...
MainList = new []
{
   new { SmallKey = v.Field3},
   new { SmallKey = v.Field4},
}

but the result is the same:

---

I think you are asking too much from Translate.

If I understand you correctly, it is the first query (mainQuery) that is too slow, so I would look to replace it.

I would create a simpler temporary class like

 public class TmpClass
 {
    public string Field1 {get;set;}
    public string Field2 {get;set;}
    public string Field3 {get;set;}
    public string Field4 {get;set;}
 } 

Once the list is in this format, you can use the second query to change it to a list of MainClass.

Just a matter of interest, what is the difference between the sql outputted by Linq and your customized version? Unless it is does some casting, I would not expect this type of query to need optimizing.

---

I would use the AsEnumerable extension method which basically converts the IQueryable to an IEnumerable which forces the enumerator to be processed. You could achieve the same thing by calling ToArray() or ToList() but AsEnumerable() magically lets you return it back to an IQueryable by calling AsQueryable()

So probably doing the following will work for you:

var result1 = DataContext.myTable.AsEnumerable()
  .Select(v=> new MainClass {
                  MainKey = v.Field1,
                  MainName = v.Field2,
                  MainList = new []
                  {
                     new SmallObject { SmallKey = v.Field3 },
                     new SmallObject { SmallKey = v.Field4 },
                  }
         });