LinQ查询具有多个表并提取数据 [英] LinQ query with multiple tables and extracting data

问题描述

我正在查询内部联接4个表，我必须提取数据并将其转换为字符串并将其放置在数组中.

I'm doing a query to inner join 4 tables and I have to extract data and convert into string and place it in an array for it.

    var query = from a in context.as
                            join b in context.bs on a.prikey equals b.forkey
                            join c in context.cs on b.prikey equals c.forkey
                            join d in context.ds on c.prikey equals d.forkey
                            where b.gender == gender
                            where c.age == age
                            select new
                            {
                                a.Name,
                                a.Age,
                                b.Gender,
                            };
string[] results = new string[] {}
return results;

通常，如果涉及单个表 a as =表a的复数

Normally, if a single table is involved a as = plural of table a

as t = query.First() 
string[] results = new string[] {t.Name, t.Age, t.Gender}
return results;

我缺少提取数据的步骤.

I'm missing a step to extract the data.

推荐答案

这取决于您要执行数据的确切操作.您的代码目前正试图编译具有多个均称为"arg"的多个属性的匿名类型，因此实际上目前无法编译，但我假设您确实有一个更明智的查询.

It depends what exactly you want to do with the data. Your code won't actually compile at the moment as it's trying to create an anonymous type with multiple properties all called "arg" but I'm assuming you've really got a more sensible query.

最终，您正在使用多个表的事实在这里是不相关的-您一次只获得一个结果元素:每个结果元素都包含来自多个表的数据的事实在这里或这里都不存在.您如何访问它.

Ultimately, the fact that you're using multiple tables is irrelevant here - you're only getting a single result element at a time: the fact that each result element contains data from multiple tables is neither here nor there in terms of how you access it.

现在，我刚刚注意到您说您想提取数据并转换为字符串".如果可能的话，您应该在查询中表达出来.您可以 在数据库中执行此操作，或者您可能需要强制执行的最后一部分在本地执行，如下所示:

Now I've just noticed that you say you want to "extract data and convert into string". If possible, you should express that in your query. You may be able to do that at the database, or you may need to force the final part of the execution to execute locally, like this:

// Not executed yet!
var dbQuery = from a in context.a
              join b in context.bs on a.prikey equals b.forkey
              join c in context.cs on b.prikey equals c.forkey
              join d in context.ds on c.prikey equals d.forkey
              where ...
              select { a.Age, b.Name, c.Salary, d.Location };

// This still won't talk to the database!
var finalQuery = dbQuery.AsEnumerable()
                        .Select(x => string.format("Age: {0}; Name: {1}; " +
                                                   "Salary: {2}; Location: {3}",
                                                   x.Age, x.Name, x.Salary,
                                                   x.Location));

// This will finally execute the query
string[] results = finalQuery.ToArray();

现在您没有没有这样的方法-但这可能是最好的方法，至少从您提供给我们的信息量来看.如果您可以告诉我们更多有关如何尝试合并多个表中数据的信息，我们可能会为您提供更多帮助.

Now you don't have to do it like this - but it's probably the best approach, at least with the amount of information you've given us. If you can tell us more about how you're trying to combine the data from the multiple tables, we may be able to help you more.

好的，现在您已经给我们提供了更多信息，我怀疑您想要这样做:

Okay, now you've given us a bit more information, I suspect you want:

var query = from a in context.a
            join b in context.bs on a.prikey equals b.forkey
            join c in context.cs on b.prikey equals c.forkey
            join d in context.ds on c.prikey equals d.forkey
            where ...
            select new string[] { a.arg, b.arg, c.arg, d.arg };

 string[] results = query.First();

我还没有尝试在LINQ to SQL中创建数组... 可能有效，或者您可能需要通过匿名类型和AsEnumerable来访问我的答案的前面部分

I haven't tried creating arrays in LINQ to SQL... that may work, or you may need to go via an anonymous type and AsEnumerable as per the earlier part of my answer.

如果没有结果或有多个结果，您还应该考虑要发生什么.

You should also think about what you want to happen if there are no results, or multiple results.

看到已编辑的问题后，您真的可以 将多个表与单个表一样对待.一旦将结果投影到匿名类型中，您将使用完全相同的代码来处理结果:

Having seen the edited question, you really can treat multiple tables the same way as a single table. You'd use exactly the same code for handling the result, once it's been projected into an anonymous type:

var query = from a in context.as
            join b in context.bs on a.prikey equals b.forkey
            join c in context.cs on b.prikey equals c.forkey
            join d in context.ds on c.prikey equals d.forkey
            where ... 
            select new { a.Name, a.Age, b.Gender };

var result = query.First();
// Call ToString appropriately on each property, of course
string[] array = new string[] { result.Name, result.Age, result.Gender };

这篇关于LinQ查询具有多个表并提取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！