linq to sql -将嵌入式sql迁移到LinqToSql vs存储过程

Question

我正在将一些代码从ColdFusion迁移到。net LinqToSQL。下面的查询目前嵌入到ColdFusion中。而不是试图让LinqToSQL做到这一点，我正在考虑编写一个存储过程。注意:在这种情况下，CF中的##实际上是SQL中的#。我没有创建全局临时表

<cfquery name="qryTrafficDetails">
    DECLARE @startDate date
    DECLARE @endDate date
    SET     @startDate = <cfqueryparam cfsqltype="CF_SQL_date" value="#filter.StartDate#">
    SET     @endDate = <cfqueryparam cfsqltype="CF_SQL_date" value="#filter.endDate#">

    SELECT  CONVERT(date, CreateDate) AS CreateDate, url_vars.value('(search)[1]', 'nvarchar(max)') AS Item, remote_addr
    INTO    ##tempTraffic
    FROM    dbo.Traffic WITH (NOLOCK)
    WHERE   Createdate BETWEEN @startDate AND @endDate
    AND     url_vars.exist('.[search]') = 1     

    SELECT Area, AreaSort, Item, CalendarDate, isNull(Visitor, 0) AS Visitor, ISNULL(Hit,0) AS Hit
    FROM    dbo.Calendar LEFT JOIN 
        (

        SELECT AreaSort = 10, 'Search' AS Area, FullGrid.Item AS Item, FullGrid.CalendarDate AS CreateDate,
                COUNT(DISTINCT remote_addr) AS visitor, 
                COUNT(remote_addr) as hit
        FROM  (
            SELECT DISTINCT CalendarDate, Item
            FROM dbo.Calendar, ##TempTraffic
            WHERE   CalendarDate  BETWEEN @startDate AND @endDate
            ) FullGrid 
        LEFT JOIN ##TempTraffic AS DataItem
            ON FullGrid.CalendarDate = CreateDate
            AND FullGrid.Item = DataItem.Item       
        GROUP BY FullGrid.Item, FullGrid.CalendarDate

        ) AS SummaryDetail
    ON  CalendarDate = SummaryDetail.CreateDate
    WHERE   CalendarDate  BETWEEN @startDate AND @endDate
    AND AreaSort IS NOT NULL    
    ORDER BY AreaSort, Item, CalendarDate
    </cfquery>
    <cfreturn qryTrafficDetails>

我关心的一些问题是:

如何处理临时表
• LinqToSql覆盖一切吗?
• 这是LinqToSql不合适的例子吗?

Answer 1

我不完全确定你的sql代码的意图和动机，但似乎是一个总结报告。在这种情况下，linq to sql可能不是理想的解决方案。

我不认为你可以从linq2sql创建临时表。您可以创建子查询，也可以使用linq 2对象来连接先前执行的查询的结果。

但是我的建议是检查是否有更好的方法来查询你的数据，使用更简洁的查询，也可以在linq2sql上执行。