如何将此Sql查询转换为Linq查询 [英] How Do I Convert This Sql Query To Linq Query

问题描述

员工表包含姓氏，名字为姓名（例如：jane，doe）



我想要的字符串来自：来自姓氏的5个字母+来自名字的3个字母+出生日期



如果姓氏少于5个字母（假设jane，doe）它应附加*

ex： jane * doe19560606 （所需输出）

employee table contains lastname,firstname as name (eg: jane,doe)

I want string which comes from: 5 letters from lastname +3 letters from firstname +date of birth

if lastname is lessthan 5 letters (suppose jane,doe) it should append with *
ex: jane*doe19560606 (required output)

SELECT left(SUBSTRING(SUBSTRING('hel,abc',1,CHARINDEX(',','hel,abc')-1),1,5)+'***',5) 
+left(SUBSTRING( 'hel,abc',CHARINDEX(',','hel,abc' )+1,3)+'***',3)+CONVERT(varchar,DATE_OF_BIRTH,112)
FROM employee

谢谢，

Thanks,

推荐答案

假设您已将数据提取到DataTable对象中，该对象包含以下数据：

Let's say you had fetched data into DataTable object, which contains data as follow:

//create example DataTable
DataTable emp = new DataTable();
//add columns
emp.Columns.Add(new DataColumn("LASTNAME", typeof(string)));
emp.Columns.Add(new DataColumn("FIRSTNAME", typeof(string)));
emp.Columns.Add(new DataColumn("DATE_OF_BIRTH", typeof(DateTime)));
//add rows
emp.Rows.Add(new Object[]{"Anne","Environ", new DateTime(1977, 12, 1)});
emp.Rows.Add(new Object[]{"Joe","Apricot", new DateTime(1978, 1, 2)});
emp.Rows.Add(new Object[]{"Maciej","Los", new DateTime(1979, 2, 3)});
emp.Rows.Add(new Object[]{"Sergey","Kryuokov", new DateTime(1980, 3, 4)});
emp.Rows.Add(new Object[]{"Carlo","Pallini", new DateTime(1981, 4, 5)});

//using "standard" query:
var query1 = (from e in emp.AsEnumerable()
    select new
        {
            NewName = (e.Field<string>("LASTNAME").Length >= 5 ? e.Field<string>("LASTNAME").Substring(0,5) : e.Field<string>("LASTNAME") + new string('*', 5- e.Field<string>("LASTNAME").Length)) +
                    e.Field<string>("FIRSTNAME").Substring(0,3) + e.Field<DateTime>("DATE_OF_BIRTH").ToString("yyyyMMdd")
        }).ToList();

//or
//using Lambda expressions
var query2 = emp.AsEnumerable()
    .Select(e=>new
        {
            NewName = (e.Field<string>("LASTNAME").Length >= 5 ? e.Field<string>("LASTNAME").Substring(0,5) : e.Field<string>("LASTNAME") + new string('*', 5- e.Field<string>("LASTNAME").Length)) +
                    e.Field<string>("FIRSTNAME").Substring(0,3) + e.Field<DateTime>("DATE_OF_BIRTH").ToString("yyyyMMdd")
        }).ToList();

foreach (var e in query1) //or query2
{
    Console.WriteLine("{0}", e.NewName);
}

结果：

Result:

NewName
Anne*Env19771201
Joe**Apr19780102
MacieLos19790203
SergeKry19800304
CarloPal19810405

试试！

Try!

这篇关于如何将此Sql查询转换为Linq查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！