使用下面的简单示例，使用Linq to SQL从多个表返回结果的最佳方法是什么?
假设我有两个表:
Dogs: Name, Age, BreedId
Breeds: BreedId, BreedName
我想返回所有的狗与他们的育种名称。我应该让所有的狗使用这样的东西，没有问题:
public IQueryable<Dog> GetDogs()
{
var db = new DogDataContext(ConnectString);
var result = from d in db.Dogs
join b in db.Breeds on d.BreedId equals b.BreedId
select d;
return result;
}
但如果我想要有品种的狗，并尝试这样做，我有问题:
public IQueryable<Dog> GetDogsWithBreedNames()
{
var db = new DogDataContext(ConnectString);
var result = from d in db.Dogs
join b in db.Breeds on d.BreedId equals b.BreedId
select new
{
Name = d.Name,
BreedName = b.BreedName
};
return result;
}
现在我意识到编译器不让我返回一组匿名类型，因为它期待狗，但有没有一种方法来返回这个而不必创建一个自定义类型?或者我必须为DogsWithBreedNames创建自己的类，并在选择中指定该类型?或者还有其他更简单的方法吗?
只是补充一下我的意见:-)
我最近学习了一种处理匿名对象的方法。它只能在针对。net 4框架时使用，并且只能在添加对System.Web.dll的引用时使用，但它非常简单:
...
using System.Web.Routing;
...
class Program
{
static void Main(string[] args)
{
object anonymous = CallMethodThatReturnsObjectOfAnonymousType();
//WHAT DO I DO WITH THIS?
//I know! I'll use a RouteValueDictionary from System.Web.dll
RouteValueDictionary rvd = new RouteValueDictionary(anonymous);
Console.WriteLine("Hello, my name is {0} and I am a {1}", rvd["Name"], rvd["Occupation"]);
}
private static object CallMethodThatReturnsObjectOfAnonymousType()
{
return new { Id = 1, Name = "Peter Perhac", Occupation = "Software Developer" };
}
}
为了能够添加对System.Web.dll的引用，你必须遵循rushonerok的建议:确保你的[项目的]目标框架是“。NET Framework 4“不是”。NET Framework 4客户端配置文件”。
您可以返回匿名类型，但这并不漂亮。
在这种情况下，我认为创建适当的类型会更好。如果只打算从包含该方法的类型中使用它，则将其设置为嵌套类型。
就我个人而言，我希望c#能够获得“命名匿名类型”——即与匿名类型相同的行为，但是有名称和属性声明，仅此而已。
EDIT: Others are suggesting returning dogs, and then accessing the breed name via a property path etc. That's a perfectly reasonable approach, but IME it leads to situations where you've done a query in a particular way because of the data you want to use - and that meta-information is lost when you just return IEnumerable<Dog> - the query may be expecting you to use (say) Breed rather than Ownerdue to some load options etc, but if you forget that and start using other properties, your app may work but not as efficiently as you'd originally envisaged. Of course, I could be talking rubbish, or over-optimising, etc...
必须首先使用ToList()方法从数据库中获取行，然后选择项作为类。
试试这个:
public partial class Dog {
public string BreedName { get; set; }}
List<Dog> GetDogsWithBreedNames(){
var db = new DogDataContext(ConnectString);
var result = (from d in db.Dogs
join b in db.Breeds on d.BreedId equals b.BreedId
select new
{
Name = d.Name,
BreedName = b.BreedName
}).ToList()
.Select(x=>
new Dog{
Name = x.Name,
BreedName = x.BreedName,
}).ToList();
return result;}
因此，诀窍首先是ToList()。它是立即进行查询并从数据库中获取数据。第二个技巧是选择项并使用对象初始化器生成加载项的新对象。
希望这能有所帮助。
