使用下面的简单示例,使用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客户端配置文件”。

其他回答

你可以这样做:


public System.Collections.IEnumerable 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.ToList();
}

在c# 7中你现在可以使用元组了!这样就不需要为了返回结果而创建一个类。

下面是一个示例代码:

public List<(string Name, string BreedName)> 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();

    return result.Select(r => (r.Name, r.BreedName)).ToList();
}

您可能需要安装System。ValueTuple nuget包。

现在我意识到编译器不让我返回一组匿名类型,因为它期待狗,但有没有一种方法来返回这个而不必创建一个自定义类型?

使用Use对象可返回匿名类型列表,而无需创建自定义类型。 这将在没有编译器错误的情况下工作(在。net 4.0中)。我将列表返回给客户端,然后在JavaScript上解析它:

public object 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;
}

如果主要的想法是让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;
        }
    }
}

这并没有完全回答你的问题,但谷歌根据关键字引导我到这里。这是从列表中查询匿名类型的方法:

var anon = model.MyType.Select(x => new { x.Item1, x.Item2});