使用linq-to-sql从多个表中检索数据

问题描述：

我们需要使用Linq-to-SQL来填充表单上的一堆控件以检索数据.为了获取数据，我们需要联接几个表.

We need to populate a bunch of controls on a form using Linq-to-SQL to retrieve the data. In order to get the data, we need to join several tables.

我们有以下方法:

第一个文件:

public IEnumerable<object> getPRs()
{
    DataContext db = new DataContext();

    var prQuery = (from p in db.Invoice
                   join prd in db.InvoiceDetails on p.ID equals prd.ID
                   join pra in Accounting on p.ID equals pra.ID
                   where 1 == 1
                   orderby p.DownloadDate descending, p.PRNumber, Convert.ToInt32(p.Item)
                   select new
                          {
                              p.ID,
                              DownloadDate = Convert.ToString(p.DownloadDate),
                              p.PRNumber,
                              p.Item,
                              p.Material,
                              p.ClientMaterial,
                              p.Description,
                              p.Client,
                              p.Price,
                              p.UC,
                              prd.Supplier,
                              prd.Duns,
                              prd.RFQ,
                              prd.RFQDate,
                              prd.PO,
                              prd.PODate,
                              POValue = prd.Price * prd.Quantity,
                              pra.SO,
                              pra.SODate,
                              pra.SOValue,
                              pra.GR,
                              pra.GRDate,
                              pra.GI,
                              pra.GIDate,
                              pra.SInvoice,
                              pra.SInvoiceDate,
                              pra.CInvoice,
                              pra.CInvoiceDate,
                              p.isActive
                          }).ToList();
    return prQuery;
}

我们在第二个文件中调用这样的方法:

And we are calling the method like this on the second file:

IEnumerable<object> data = FirstFile.GetPRs();
PRNumberTextBox.Text = data.PRNumber;

最后一行将给出错误，因为我们无法从数据对象访问PRNumber成员.为了填写所有文本框，我们如何调用函数并添加必要的信息?

The last line will give an error because we can't access the PRNumber member from the data object. In order to fill in all the text boxes, how can we call our function and add the necessary info?

答

您正在返回匿名类型.我建议您创建一个模型类以返回数据:

You are returning an anonymous type. I would recommend you create a model class to return your data:

public class PRModel
{
    public int ID { get; set; }
    public string DownloadDate { get; set; }
    //etc... you can fill in the rest yourself
}

然后您的查询将变成这样:

Then your query becomes something like this:

var prQuery = (from p in db.Invoice
               join prd in db.InvoiceDetails on p.ID equals prd.ID
               join pra in Accounting on p.ID equals pra.ID
               where 1 == 1
               orderby p.DownloadDate descending, p.PRNumber, Convert.ToInt32(p.Item)
               select new PRModel //<---This is the change here
               {
                   ID = p.ID,
                   DownloadDate = Convert.ToString(p.DownloadDate),
                   PRNumber = p.PRNumber,
                   //snipp
               }

最后，您的返回类型现在应该为IEnumerable<PRModel>.另外，由于这是一个枚举(即您可能有0、1或许多项)，因此您需要遍历它们:

And finally, your return type should now be IEnumerable<PRModel>. Also, as it's an enumeration (i.e. you may have 0, 1 or many items) you need to iterate through them:

IEnumerable<PRModel> data = FirstFile.GetPRs();
foreach(var pr in data)
{
    PRNumberTextBox.Text = pr.PRNumber;
}

在这种情况下，如果您只想退回单个发票，则更容易完成.而不是这样做:

In this case, if you only want to return an individual invoice, it's even easier to do. Instead of doing this:

select new PRModel
{
    //lots of rows
}

您可以简单地执行以下操作:

You could simply do this:

select p

这又使您的返回类型为IEnumerable<Invoice>.

Which in turn makes your return type IEnumerable<Invoice>.

使用linq-to-sql从多个表中检索数据

相关推荐