c# - 如何使用 LINQ 像 SQL 一样查询 CSV？

Question

现在我首先读取 CSV 文件并将数据加载到 datatabel 中，然后将该数据表中的数据插入到 sql 表中。在将数据转储到 sql 表中之后，我使用 sql 获取数据并使用许多带有 where 的子句进行过滤。

如何使用 LINQ 读取和查询包含许多过滤子句的 CSV 文件并将结果转储到数据表中。

在这里，我将在将 csv 数据转储到 db 表中后用于从 db 表中获取数据的 sql。 sql 是在 c# 应用程序中生成的。

        strSql = "select (select count(*) as incoming from " + tableName + " where direction='I' and ";
        strSql = strSql + "CONVERT(datetime,right([Call Start],8)) >='" + StartTime + "' and ";
        strSql = strSql + "CONVERT(datetime,right([Call Start],8)) <='" + EndTime + "' ";
        strSql = strSql + "and Is_Internal=0 and continuation=0 and RIGHT(convert(varchar,[call duration]),8)<> '00:00:00' ";
        strSql = strSql + "and party1name not in ('Voice Mail') and party1name not like 'VM %') as incoming, ";

        strSql = strSql + "(select count(*) as OutGoing from " + tableName + " ";
        strSql = strSql + "where direction='O' and ";
        strSql = strSql + "CONVERT(datetime,right([Call Start],8)) >='" + StartTime + "' and ";
        strSql = strSql + "CONVERT(datetime,right([Call Start],8)) <='" + EndTime + "' ";
        strSql = strSql + "and Is_Internal=0 and continuation=0  and party1name not in ('Voice Mail') ";
        strSql = strSql + "and party1name not like 'VM %') as OutGoing, ";

        strSql = strSql + "(select count(*) as CallTransfer from " + tableName + " ";
        strSql = strSql + "where continuation=1  and ";
        strSql = strSql + "CONVERT(datetime,right([Call Start],8)) >='" + StartTime + "' and ";
        strSql = strSql + "CONVERT(datetime,right([Call Start],8)) <='" + EndTime + "' ";
        strSql = strSql + "and RIGHT(convert(varchar,[call duration]),8)<> '00:00:00' and party1name not in ('Voice Mail') ";
        strSql = strSql + "and party1name not like 'VM %') as CallTransfer; ";

        strSql = strSql + "SELECT count(*) as UnansweredCalls_DuringBusinessHours from "
                        + tableName + " where direction='I' and " + Environment.NewLine;
        strSql = strSql + "CONVERT(datetime,right([Call Start],8)) >='" + StartTime + "' and ";
        strSql = strSql + "CONVERT(datetime,right([Call Start],8)) <='" + EndTime + "' ";
        strSql = strSql + "and RIGHT(convert(varchar,[call duration]),8)= '00:00:00' and [Ring duration]>0 " + Environment.NewLine;
        //strSql = strSql + "CONVERT(Varchar,CONVERT(datetime,[Call Start]),108) between '09:00:00' and '17:30:00' " + Environment.NewLine;
        strSql = strSql + "and party1name not in ('Voice Mail')  and party1name not like 'VM %' and party1name not like 'Line%'" + Environment.NewLine;

所以请任何人看到 sql 并告诉我如何在通过 LINQ 查询 csv 文件时使用相同的子句。

请告诉我需要编写什么代码来读取 csv 文件以及查询 csv 文件中的数据。请指导。谢谢

Answer 1

我建议使用像 https://www.nuget.org/packages/LINQtoCSV/ 这样的库

它很有特色，会稍微简化您的生活。

所以你需要声明你的对象，所以

public CallReportLine {

[CsvColumn(Name = "Call Start", FieldIndex = 1)]
public DateTime CallStart {get; set;}

}

然后加载它

CsvContext cc = new CsvContext();
IEnumerable<CallReportLine> calls =    cc.Read<Product>("MyFirstCSV.csv");

然后像

calls.Where(call => call.CallStart >= StartTime && call.CallStart < EndTime)