使用下面的简单示例，使用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创建自己的类，并在选择中指定该类型?或者还有其他更简单的方法吗?
BreedId in the Dog table is obviously a foreign key to the corresponding row in the Breed table. If you've got your database set up properly, LINQ to SQL should automatically create an association between the two tables. The resulting Dog class will have a Breed property, and the Breed class should have a Dogs collection. Setting it up this way, you can still return IEnumerable<Dog>, which is an object that includes the breed property. The only caveat is that you need to preload the breed object along with dog objects in the query so they can be accessed after the data context has been disposed, and (as another poster has suggested) execute a method on the collection that will cause the query to be performed immediately (ToArray in this case):
public IEnumerable<Dog> GetDogs()
{
using (var db = new DogDataContext(ConnectString))
{
db.LoadOptions.LoadWith<Dog>(i => i.Breed);
return db.Dogs.ToArray();
}
}
然后，访问每只狗的品种是微不足道的:
foreach (var dog in GetDogs())
{
Console.WriteLine("Dog's Name: {0}", dog.Name);
Console.WriteLine("Dog's Breed: {0}", dog.Breed.Name);
}
如果主要的想法是让SQL选择语句发送到数据库服务器只有必需的字段，而不是所有的实体字段，那么你可以这样做:
public class Class1
{
public IList<Car> getCarsByProjectionOnSmallNumberOfProperties()
{
try
{
//Get the SQL Context:
CompanyPossessionsDAL.POCOContext.CompanyPossessionsContext dbContext
= new CompanyPossessionsDAL.POCOContext.CompanyPossessionsContext();
//Specify the Context of your main entity e.g. Car:
var oDBQuery = dbContext.Set<Car>();
//Project on some of its fields, so the created select statment that is
// sent to the database server, will have only the required fields By making a new anonymouse type
var queryProjectedOnSmallSetOfProperties
= from x in oDBQuery
select new
{
x.carNo,
x.eName,
x.aName
};
//Convert the anonymouse type back to the main entity e.g. Car
var queryConvertAnonymousToOriginal
= from x in queryProjectedOnSmallSetOfProperties
select new Car
{
carNo = x.carNo,
eName = x.eName,
aName = x.aName
};
//return the IList<Car> that is wanted
var lst = queryConvertAnonymousToOriginal.ToList();
return lst;
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
throw;
}
}
}
