c# - 在 C# 中对两个 CSV 文件进行复合连接
问题描述
从每日水果价格表开始
水果.csv
Day,Name,Kind,Price
2019-09-04,"apple","red",63.09
2019-09-04,"apple","yellow",52.14
2019-09-04,"orange","navel",41.18
2019-09-04,"orange","blood",41.18
2019-09-03,"apple","red",63.07
2019-09-03,"apple","yellow",52.11
2019-09-03,"orange","navel",41.13
2019-09-03,"orange","blood",41.13
我想按名称和种类插入参考价格
fruit_ref_prices.csv
Name,Kind,Reference_Price
"apple","red",60.00
"apple","yellow",50.00
"orange","navel",40.00
"orange","blood",42.00
得到下表
Day,Name,Kind,Price,Reference_Price
2019-09-04,"apple","red",63.09,60.00
2019-09-04,"apple","yellow",52.14,50.00
2019-09-04,"orange","navel",41.18,40.00
2019-09-04,"orange","blood",41.18,42.00
2019-09-03,"apple","red",63.07,60.00
2019-09-03,"apple","yellow",52.11,50.00
2019-09-03,"orange","navel",41.13,40.00
2019-09-03,"orange","blood",41.13,42.00
使用 C# 内置的类似 SQL 的语法,解决方案应该很简单,我确信答案在于以下教程页面之一:
但我很难确定这种语言的语法。
在我下面的尝试中,而不是写作
join fruit_ref in fruit_refs on fruit.name equals fruit_ref.name
我应该可以写
join fruit_ref in fruit_refs on fruit.name equals fruit_ref.name
and fruit.kind equals fruit_ref.kind
但不接受布尔表达式。为什么?
我的尝试是:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
namespace MyConsoleApplication
{
class Program
{
const string root = @"c:\path\to\here\";
const string file1_in = root + @"fruits.csv";
const string file2_in = root + @"fruit_ref_prices.csv";
static void Main(string[] args)
{
Fruit_Basket fruit_basket = new Fruit_Basket(file1_in, file2_in);
fruit_basket.PrintFruits();
}
}
public class Fruit
{
public DateTime day { get; set; }
public string name { get; set; }
public string kind { get; set; }
public decimal price { get; set; }
public Fruit(DateTime newFruit_day,
string newFruit_name,
string newFruit_kind,
decimal newFruit_price)
{
this.day = newFruit_day;
this.name = newFruit_name;
this.kind = newFruit_kind;
this.price = newFruit_price;
}
}
public class Fruit_Ref
{
public string name;
public string kind;
public decimal reference_price;
public Fruit_Ref(string newName, string newKind, decimal newRef_Price)
{
this.name = newName;
this.kind = newKind;
this.reference_price = newRef_Price;
}
}
public class Fruit_Basket {
public List<Fruit> fruits { get; set; }
public List<Fruit_Ref> fruit_refs { get; set; }
public Fruit_Basket(string file1_in, string file2_in) {
build_fruit_list(file1_in);
build_fruit_ref_list(file2_in);
}
public void build_fruit_list(string file_in)
{
fruits = new List<Fruit>();
int count = 0;
StreamReader reader = new StreamReader(file_in);
string line = "";
while ((line = reader.ReadLine()) != null)
{
if (++count > 1)
{
string[] splitLine = line.Split(new char[] { ',' }).ToArray();
var newFruit_day = DateTime.Parse(splitLine[0]);
var newFruit_name = splitLine[1];
var newFruit_kind = splitLine[2];
var newFruit_price = decimal.Parse(splitLine[3]);
Fruit newFruit = new Fruit(newFruit_day,
newFruit_name,
newFruit_kind,
newFruit_price);
fruits.Add(newFruit);
}
}
reader.Close();
}
public void build_fruit_ref_list(string file_in)
{
fruit_refs = new List<Fruit_Ref>();
int count = 0;
StreamReader reader = new StreamReader(file_in);
string line = "";
while ((line = reader.ReadLine()) != null)
{
if (++count > 1)
{
string[] splitLine = line.Split(new char[] { ',' }).ToArray();
var newFruit_name = splitLine[0];
var newFruit_kind = splitLine[1];
var newFruit_ref_price = decimal.Parse(splitLine[2]);
Fruit_Ref newFruit_ref = new Fruit_Ref(newFruit_name,
newFruit_kind,
newFruit_ref_price);
fruit_refs.Add(newFruit_ref);
}
}
reader.Close();
}
public void PrintFruits()
{
var innerJoinQuery =
from fruit in fruits
join fruit_ref in fruit_refs on fruit.name equals fruit_ref.name
select new { Day = fruit.day, Name = fruit.name, Kind = fruit.kind,
Price = fruit.price, Reference_Price = fruit_ref.reference_price };
Console.WriteLine($@"""Date"",""Name"",""Kind"",""Price"",""Ref Price""");
foreach (var i in innerJoinQuery)
{
Console.WriteLine($@"{i.Day},{i.Kind},{i.Price},{i.Reference_Price}");
}
}
}
}
解决方案
请将equals子句更改为on new { fruit.name, fruit.kind } equals new { fruit_ref.name, fruit_ref.kind }
为什么你需要这个
该查询有两种匿名类型(一种用于左表,一种用于右表)。所以要比较那些匿名类型,linq 语句应该使用 new 关键字
询问 :
var innerJoinQuery = from fruit in fruits
join fruit_ref in fruit_refs on new { fruit.name, fruit.kind } equals new { fruit_ref.name, fruit_ref.kind }
select new { Day = fruit.day, Name = fruit.name, Kind = fruit.kind,
Price = fruit.price, Reference_Price = fruit_ref.reference_price };
推荐阅读
- android - 检测到 Android Studio 设备但未识别为 Android 设备(ADB 故障)
- c - 显示控制PIC24FJ128GC010
- flutter - 缺少 (state.build) sqflite 的具体实现
- python - 我想使用 python 将数据从 word 表移动到 excel 表但不能这样做,当 Table 中有子列时我会遇到错误
- lua - 波浪号本身在 Lua 中是什么意思?
- r - 如何提取列表的每个第 n 个元素
- python - 通过分箱聚合间隔数据以创建时间序列
- ruby-on-rails - Rails Active Storage 可以映射到数据库中预先存在的图像表吗?
- python - 需要帮助使用 selenium python 和 2captcha 创建 Outlook 帐户 - Funcaptcha
- javascript - Laravel - 在 Javascript / React 组件中使用翻译