DataContractSerializer within LINQ to SQL expression?

Hi, I'm new to LINQ expressions and trying to get the following to work:

public IQueryable<Models.Powerups.Powerup> GetPowerups(Guid userid)
{
 return from p in dc.Powerups
     where p.UserId == userid
     select (Models.Powerups.Powerup) new DataContractSerializer(Type.GetType(p.Type, true)).ReadObject(new XmlTextReader(new StringReader(p.Data)));
}

It obviously doesn't work since LINQ is trying to translate the entire expression to SQL.

Do you know a way to do this?

From stackoverflow

• The best thing would be to do this:

  public IQueryable<Models.Powerups.Powerup> GetPowerups(Guid userid)
  {
          return from p in dc.Powerups
                     where p.UserId == userid
                     select p.Data;
  }
  

  Then deserialize the xml yourself once you get it back from the database. The LINQ to SQL provider will not know how to turn this into SQL and you wouldn't want it to anyways. The deserialization of serialized objects ought to happen in the CLR anyhow.

• You need to break this into two parts, the first to get the data from the database, and then the transformation outside of that query for the data. You can use auto-generated iterators in C# to help:

  public IEnumerable<Models.Powerups.Powerup> GetPowerups(Guid userid)
  {
    // Create the serializer here.
    DataContractSerializer s = new DataContractSerializer(typeof(Models.Powerups.Powerup));
  
    // Iterate through the powerups.
    foreach (var p in dc.Powerups)
    {
      // Create the string reader, xml reader, then deserialize and return
      // instance.
      using (StringReader stringReader = new StringReader(p.Data))
      using (XmlTextReader xmlTextReader = new XmlTextReader(stringReader))
      {
        // Return the deserialized instance.
        yield return (Models.Powerups.Powerup) s.ReadObject(xmlTextReader);
      }
    }
  }
  

  The great thing about this is that it allows for deferred execution as well.