{"id":921,"date":"2017-11-03T15:44:14","date_gmt":"2017-11-03T14:44:14","guid":{"rendered":"https:\/\/solidt.eu\/site\/?p=921"},"modified":"2021-10-25T11:15:49","modified_gmt":"2021-10-25T10:15:49","slug":"linq2sql","status":"publish","type":"post","link":"https:\/\/solidt.eu\/site\/linq2sql\/","title":{"rendered":"Linq2db sql"},"content":{"rendered":"\n

https:\/\/discoverdot.net\/projects\/linq2db<\/a><\/p>\n\n\n\n

var builder = MappingSchema.Default.GetFluentMappingBuilder();\n\nbuilder.Entity<Product>()\n    .HasTableName(\"Products\")\n    .HasSchemaName(\"dbo\")\n    .HasIdentity(x => x.ProductID)\n    .HasPrimaryKey(x => x.ProductID)\n    .Ignore(x => x.SomeNonDbProperty)\n    .Property(x => x.TimeStamp)\n        .HasSkipOnInsert()\n        .HasSkipOnUpdate()\n    .Association(x => x.Vendor, x => x.VendorID, x => x.VendorID, canBeNull: false)\n    ;\n\n\/\/... other mapping configurations<\/pre><\/div>\n\n\n\n
using IBM.Data.DB2.iSeries;\nusing LinqToDB;\nusing LinqToDB.DataProvider;\nusing LinqToDB.DataProvider.DB2;\nusing LinqToDB.Mapping;\nusing System.Data;\n\n    public class DbIseries : LinqToDB.Data.DataConnection\n    {\n        public DbIseries() : base(GetDataProvider(), GetConnection()) { }\n\n        public ITable Product => GetTable();\n\n        public ITable ArtikelGroep => GetTable();\n\n        private static IDataProvider GetDataProvider()\n        {\n            return new DB2DataProvider(\"test.wan\", DB2Version.zOS);\n        }\n\n        private static IDbConnection GetConnection()\n        {\n            LinqToDB.Common.Configuration.AvoidSpecificDataProviderAPI = true;\n\n            var dbConnection = new iDB2Connection(@\"DataSource=test.wan;UserID=user;Password=password;DataCompression=True;DEFAULT COLLECTION=ABC;Pooling=false;\");\n            return dbConnection;\n        }\n\n    }\n    \n    [Table(Name = \"GS01HGR\")]\n    public class Product\n    {\n        [PrimaryKey, Identity, Column(Name = \"HGRNUM\")]\n        public int ProductId { get; set; }\n\n        [Column(Name = \"HGROMS\"), NotNull]\n        public string Name { get; set; }\n    }\n\n    [Table(Name = \"GS01AGR\")]\n    public class ArtikelGroep\n    {\n        [PrimaryKey, Identity, Column(Name = \"AGRNUM\")]\n        public int ArtikelGroepId { get; set; }\n\n        [Column(Name = \"AGROMS\"), NotNull]\n        public string Name { get; set; }\n\n        [Column(Name = \"AGRHGR\"), NotNull]\n        public int HoofdGroepId { get; set; }\n    }\n\n        static void Main(string[] args)\n        {\n                try\n            {\n                using (var db = new DbIseries())\n                {\n                    var data = db.Product\n                            .Join(db.ArtikelGroep,\n                                a => a.ProductId,\n                                b => b.HoofdGroepId,\n                                (a, b) => new { a, b })\n                            .Where(p => p.a.ProductId < 25).OrderBy(p => p.a.Name);\n                    var records = data.ToList();\n\n                    String results = JsonConvert.SerializeObject(records, Formatting.Indented);\n                    Console.WriteLine(results); ;\n                }\n\n            }\n            catch (Exception e)\n            {\n                Console.WriteLine(e);\n                throw;\n            }\n            Console.ReadKey();\n\n        }<\/pre>\n\n\n\n
private static MappingSchema GetMappingSchema()\n{\n    var mappingSchema = new MappingSchema();\n    var mappers = typeof(BaseAfwezigheidRepository<T>).Assembly.GetTypes().Where(t => t.BaseType == typeof(BaseDataMapper<T>));\n    foreach(var mapper in mappers) { \n        Activator.CreateInstance(mapper, mappingSchema);\n    }\n    return mappingSchema;\n}\n\npublic class BaseDataMapper<T>\n{\n    protected readonly EntityMappingBuilder<T> Map;\n\n    protected BaseDataMapper(MappingSchema mappingSchema)\n    {\n        Map = mappingSchema.GetFluentMappingBuilder().Entity<T>();\n    }\n}<\/pre>\n\n\n\n
Creating your POCOs<\/h2>\n\n\n\n
In the previous example we assign an entire Category<\/code> object to our product, but what if we want all the fields in our Product<\/code> class, but we don’t want to specify every field by hand? Unfortunately, we cannot<\/strong> write this:<\/p>\n\n\n\n
from p in db.Product\nfrom c in db.Category.Where(q => q.CategoryID == p.CategoryID).DefaultIfEmpty()\nselect new Product(c);<\/pre>\n\n\n\n
The query above assumes the Product class has a constructor that takes in a Category object. The query above won’t work, but we can<\/strong> work around that with the following query:<\/p>\n\n\n\n
from p in db.Product\nfrom c in db.Category.Where(q => q.CategoryID == p.CategoryID).DefaultIfEmpty()\nselect Product.Build(p, c);<\/pre>\n\n\n\n
For this to work, we need a function in the Product<\/code> class that looks like this:<\/p>\n\n\n\n
public static Product Build(Product product, Category category)\n{\n  if (product != null)\n  {\n    product.Category = category;\n  }\n  return product;\n}<\/pre>\n\n\n\n
One caveat with this approach is that if you’re using it with composed queries (see example above) the select Build part has to come only in the final select.<\/p>\n","protected":false},"excerpt":{"rendered":"
https:\/\/discoverdot.net\/projects\/linq2db Creating your POCOs In the previous example we assign an entire Category object to our product, but what if we want all the fields in our Product class, but we don’t want to specify every field by hand? Unfortunately, we cannot write this: from p in db.Product from c in db.Category.Where(q => q.CategoryID == p.CategoryID).DefaultIfEmpty() select new Product(c); The query […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"footnotes":""},"categories":[6],"tags":[],"class_list":["post-921","post","type-post","status-publish","format-standard","hentry","category-dotnet"],"_links":{"self":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts\/921","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/comments?post=921"}],"version-history":[{"count":8,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts\/921\/revisions"}],"predecessor-version":[{"id":5497,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts\/921\/revisions\/5497"}],"wp:attachment":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/media?parent=921"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/categories?post=921"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/tags?post=921"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}