在REST/WCF服务中将数据集从LINQ返回到SQL [英] Returning datasets from LINQ to SQL in a REST/WCF service

问题描述

我有一个WCF/REST Web服务，正在考虑使用Linq to SQL从中返回数据库信息.

I have a WCF/REST web service that I'm considering using Linq to SQL to return database info from.

对表进行基本查询并返回行很容易，例如:

It's easy enough to do basic queries against tables and return rows, for example:

    [WebGet(UriTemplate = "")]
    public List<User> GetUsers()
    {
        List<User> ret = new List<User>(); ;
        using (MyDataContext context = new MyDataContext())
        {
            var userResults = from u in context.Users select u;
            ret = userResults.ToList<User>();
        }

        return ret;
    }

但是，如果我想从多个表中返回数据或与表的架构不完全匹配该怎么办?我不知道如何从此查询返回结果，例如:

But what if I want to return data from multiple tables or that doesn't exactly match the schema of the table? I can't figure out how to return the results from this query, for example:

 var userResults = from u in context.Users
   select new  { u.userID, u.userName, u.userType, 
                 u.Person.personFirstname, u.Person.personLastname };

很明显，结果行集不遵循用户"模式，因此我不能只转换为用户对象列表.

Obviously the resulting rowset doesn't adhere to the "User" schema, so I can't just convert to a list of User objects.

我尝试在对象模型中创建一个与结果集相关的新实体，但它不想进行转换.

I tried making a new entity in my object model that related to the result set, but it doesn't want to do the conversion.

我想念什么?

相关问题:存储过程返回的结果如何?同样的问题，打包它们以便通过服务返回的最佳方法是什么?

related question: what about results returned from stored procedures? Same issue, what's the best way to package them up for returning via the service?

推荐答案

您可以创建

You can create a Complex Type and instead of returning Anonymous object you return the Complex Type. When you map stored procedures using function import, you have a option to automatically create a complex type.

使用所需的属性创建一个自定义类:

Create a custom class with the properties that you need:

public class MyTimesheet
{
    public int Id { get; set; }
    public string Data { get; set; }
}

然后从您的Linq查询中创建它:

Then create it from your Linq query:

using (linkDataContext link = new linkDataContext())
{
    var data = (from t in link.TimesheetDetails
               select new MyTimesheet
               {
                   Id = t.Id,
                   Data = t.EmployeeId.ToString()
               }).ToList();
}

这篇关于在REST/WCF服务中将数据集从LINQ返回到SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！