以下是我自定义的两个表实例,我目前用的是LinqToSQlite来直接实例化的
//---------------------------------------------------------------------------------------------------
// <auto-generated>
// This code was generated by T4Model template for T4 (https://github.com/linq2db/t4models).
// Changes to this file may cause incorrect behavior and will be lost if the code is regenerated.
// </auto-generated>
//---------------------------------------------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Linq;
using LinqToDB;
using LinqToDB.Mapping;
namespace DataModels
{
/// <summary>
/// Database : meter
/// Data Source : meter
/// Server Version : 3.8.8.3
/// </summary>
public partial class meterDB : LinqToDB.Data.DataConnection
{
public ITable<cmdItemDlm> cmdItemDlms { get { return this.GetTable<cmdItemDlm>(); } }
public ITable<TypeCode> TypeCodes { get { return this.GetTable<TypeCode>(); } }
public meterDB()
{
InitDataContext();
}
public meterDB(string configuration)
: base(configuration)
{
InitDataContext();
}
partial void InitDataContext();
}
[Table("cmdItemDlms")]
public partial class cmdItemDlm
{
[PrimaryKey, NotNull ] public string cmdId { get; set; } // char(max)
[Column, Nullable] public string FieldId { get; set; } // char(max)
[Column, Nullable] public string LanguageId { get; set; } // char(max)
[Column, Nullable] public string TypeCode { get; set; } // char(max)
[Column, Nullable] public int? classid { get; set; } // int
[Column, Nullable] public byte? A { get; set; } // tinyint
[Column, Nullable] public byte? B { get; set; } // tinyint
[Column, Nullable] public byte? C { get; set; } // tinyint
[Column, Nullable] public byte? D { get; set; } // tinyint
[Column, Nullable] public byte? E { get; set; } // tinyint
[Column, Nullable] public byte? F { get; set; } // tinyint
[Column, Nullable] public string obis { get; set; } // char(max)
}
[Table("TypeCode")]
public partial class TypeCode
{
[Column("TypeCode"), PrimaryKey, NotNull] public string TypeCode_Column { get; set; } // char(max)
[Column(), Nullable ] public string LanguageId { get; set; } // char(max)
[Column(), Nullable ] public string ParentCode { get; set; } // char(max)
[Column(), Nullable ] public string description { get; set; } // char(max)
#region Associations
/// <summary>
/// FK_TypeCode_0_0
/// </summary>
[Association(ThisKey="LanguageId", OtherKey="LanguageId", CanBeNull=true, KeyName="FK_TypeCode_0_0", BackReferenceName="TypeCode00")]
public LangItem FK_00 { get; set; }
#endregion
}
public static partial class TableExtensions
{
public static cmdItemDlm Find(this ITable<cmdItemDlm> table, string cmdId)
{
return table.FirstOrDefault(t =>
t.cmdId == cmdId);
}
public static TypeCode Find(this ITable<TypeCode> table, string TypeCode_Column)
{
return table.FirstOrDefault(t =>
t.TypeCode_Column == TypeCode_Column);
}
}
}
因此在用linq连接两个表实例typecode和cmditemDmls时的linq语句是这样的
var db = new DataModels.meterDB();
var listTemp = from tempCode in db.TypeCode
join dlmsCode in db.CmdItemDlm
on tempCode.TypeCode equals dlmsCode.TypeCode
into codes
from code in codes.DefaultIfEmpty(new DataModels.CmdItemDlm
{
A = 0,
B = 0,
C = 0,
D = 0,
E = 0,
F= 0,
Classid = 3,
FieldId = "",
CmdId = "",
Obis = "",
LanguageId = "",
TypeCode = ""
})
select new
{
type_code = tempCode.TypeCode,
language_Id = tempCode.LanguageId,
parent_code = tempCode.ParentCode,
dlms_code = code.Obis,
filed_id = code.FieldId,
language_cmdId = code.LanguageId,
cmd_id = code.CmdId
};
<span style="white-space:pre"> </span>
其中后面的DefaultIfEmpty(new {…………})而且必须要有,否则后面遍历ListTemp的时候会点不出对应的属性!
这是我个人的经验,不喜勿喷,欢迎提出宝贵的意见!