c# - LINQ中可以用什么代替Datatable

Question

我有一个返回数据表的 SQL 查询:

var routesTable = _dbhelper.Select("SELECT [RouteId],[UserId],[SourceName],[CreationTime] FROM [Routes] WHERE UserId=@UserId AND RouteId=@RouteId", inputParams);

然后我们可以使用routesTable 的数据表对象

if (routesTable.Rows.Count == 1)
            {
                result = new Route(routeId)
                {
                    Name = (string)routesTable.Rows[0]["SourceName"],
                    Time = routesTable.Rows[0]["CreationTime"] is DBNull ? new DateTime() : Convert.ToDateTime(routesTable.Rows[0]["CreationTime"])
                };

                result.TrackPoints = GetTrackPointsForRoute(routeId);
            }

我想将此代码更改为 linq 但我不知道如何在 LINQ 中模拟数据表，我写了这部分:

Route result = null;
            aspnetdbDataContext aspdb = new aspnetdbDataContext();
            var Result = from r in aspdb.RouteLinqs
                           where r.UserId == userId && r.RouteId==routeId
                           select r;


    ....

但我不知道如何更改这部分:

if (routesTable.Rows.Count == 1)
            {
                result = new Route(routeId)
                {
                    Name = (string)routesTable.Rows[0]["SourceName"],
                     Time = routesTable.Rows[0]["CreationTime"] is DBNull ? new DateTime() : Convert.ToDateTime(routesTable.Rows[0]["CreationTime"])
                };

你能告诉我我该怎么做吗？

编辑在这里你可以看到原始的整个代码块

public Route GetById(int routeId, Guid userId)
        {
            Route result = null;
            var inputParams = new Dictionary<string, object>
                                  {
                                      {"UserId", userId},
                                      {"RouteId", routeId}
                                  };

            var routesTable = _dbhelper.Select("SELECT [RouteId],[UserId],[SourceName],[CreationTime] FROM [Routes] WHERE UserId=@UserId AND RouteId=@RouteId", inputParams);

            if (routesTable.Rows.Count == 1)
            {
                result = new Route(routeId)
                {
                    Name = (string)routesTable.Rows[0]["SourceName"],
                    Time = routesTable.Rows[0]["CreationTime"] is DBNull ? new DateTime() : Convert.ToDateTime(routesTable.Rows[0]["CreationTime"])
                };

                result.TrackPoints = GetTrackPointsForRoute(routeId);
            }

            return result;
        }

SELECT 函数:

public DataTable Select(string query, Dictionary<string, object> parameters)
        {
            var dt = new DataTable();

            using (_command = new SqlCommand(query, _connnection))
            {
                InitializeParametersAndConnection(parameters);

                using (_adapter = new SqlDataAdapter(_command))
                {
                    _adapter.Fill(dt);
                }
            }

            return dt;
        }

和 GetTrackPointsForRoute

private List<TrackPoint> GetTrackPointsForRoute(int routeId)
        {
            aspnetdbDataContext aspdb = new aspnetdbDataContext();
            var result = new List<TrackPoint>();
            var trackPointsTable = from t in aspdb.TrackPointlinqs
                                   where t.RouteFK == routeId
                                   select t;
            foreach (var trackPointRow in trackPointsTable)
            {
                var trackPoint = new TrackPoint
                {
                    Id = (int)trackPointRow.TrackPointId,
                    Elevation = Convert.ToSingle(trackPointRow.Elevation),
                    Latitude = Convert.ToDouble(trackPointRow.Latitude),
                    Longitude = Convert.ToDouble(trackPointRow.Longitude),
                    Time = trackPointRow.TrackTime is DBNull ? new DateTime() : (DateTime)trackPointRow.TrackTime
                };
                result.Add(trackPoint);
            }

            return result;
        }

Answer 1

var firstRoute = aspdb.RouteLinqs
    .Where(r => r.UserId == userId && r.RouteId == routeId)
    .FirstOrDefault();

if (firstRoute == null)
{
    return null;
}
else
{
    return new Route(routeId)
    {
        Name = first.SourceName,
        Time = first.CreationTime ?? new DateTime(),
        TrackPoints = GetTrackPointsForRoute(routeId)
    };
}

如果这是 LINQ to SQL，您可以进一步简化它(尽管这不适用于 LINQ to Entity Framework):

return aspdb.RouteLinqs
    .Where(r => r.UserId == userId && r.RouteId == routeId)
    .Select(r => new Route(routeId)
    {
        Name = r.SourceName,
        Time = r.CreationTime ?? new DateTime(),
        TrackPoints = GetTrackPointsForRoute(routeId)
    })
    .FirstOrDefault();

注意:您可能可以将 GetTrackPointsForRoute 替换为连接到子表，这意味着整个方法可以通过对数据库的一次调用来完成，而不是一次调用来获取路线，和第二个电话来获得积分。为此，您应该了解 LINQ to SQL 中的关联和联接。