none
linq Join RRS feed

  • Question

  • how to join : 
    --------------------------------------------
    Product_T.cs

    using System.Data.Linq;
    using System.Data.Linq.Mapping;
    using System.Drawing;
    [Table(Name = "Product_T")]
        public class Product_T
    {
        private int _Pro_id;
        private string _Prod_barcode;
        private string _Prod_name;
        private int _Unit_id;
        private int _Quantity;
        private int _Pro_remain_max;
        private int _Pro_remain_min;
        private int _Unit_price;
        private int _Prod_group_id;
        private string _Prod_detail1;
        private string _Prod_detail2;
        private string _Prod_detail3;
        private string _Prod_stock;
        private string _Prod_remark;
        private Image _Prod_pic;

        [Column(Name = "Pro_id", AutoSync = AutoSync.OnInsert, DbType = "Int NOT NULL IDENTITY", IsPrimaryKey = true, IsDbGenerated = true)]
        public int Pro_id
        {
            get{return _Pro_id;}
            set{_Pro_id = value;}
        }
        [Column(Name = "Prod_barcode")]
        public string Pro_barcode {
            get { return _Prod_barcode; }
            set { _Prod_barcode = value; }
        }
        [Column(Name = "Prod_name")]
        public string Pro_name
        {
            get { return _Prod_name; }
            set { _Prod_name = value; }
        }
        [Column(Name = "Unit_id")]
        public int Unit_id
        {
            get { return _Unit_id; }
            set { _Unit_id = value; }
        }
        [Column(Name = "Quantity")]
        public int Quantity
        {
            get { return _Quantity; }
            set { _Quantity = value; }
        }
        [Column(Name = "Pro_remain_max")]
        public int Pro_remain_max
        {
            get { return _Pro_remain_max; }
            set { _Pro_remain_max = value; }
        }
        [Column(Name = "Pro_remain_min")]
        public int Pro_remain_min
        {
            get { return _Pro_remain_min; }
            set { _Pro_remain_min = value; }
        }
        [Column(Name = "Unit_price")]
        public int Unit_price
        {
            get { return _Unit_price; }
            set { _Unit_price = value; }
        }
        [Column(Name = "Prod_group_id")]
        public int Prod_group_id
        {
            get { return _Prod_group_id; }
            set { _Prod_group_id = value; }
        }
        [Column(Name = "Prod_detail1")]
        public string Prod_detail1
        {
            get { return _Prod_detail1; }
            set { _Prod_detail1 = value; }
        }
        [Column(Name = "Prod_detail2")]
        public string Prod_detail2
        {
            get { return _Prod_detail2; }
            set { _Prod_detail2 = value; }
        }
        [Column(Name = "Prod_detail3")]
        public string Prod_detail3
        {
            get { return _Prod_detail3; }
            set { _Prod_detail3 = value; }
        }
        [Column(Name = "Prod_stock")]
        public string Prod_stock
        {
            get { return _Prod_stock; }
            set { _Prod_stock = value; }
        }
        [Column(Name = "Prod_remark")]
        public string Prod_remark
        {
            get { return _Prod_remark; }
            set { _Prod_remark = value; }
        }
        [Column(Name = "Prod_pic")]
        public Image Prod_pic
        {
            get { return _Prod_pic; }
            set { _Prod_pic = value; }
        }


    }
    -------------------------------------------------------------------------
    ProductGroup.cs
    using System.Data.Linq;
    using System.Data.Linq.Mapping;

    [Table(Name = "ProductGroup")]
    public class ProductGroup
    {
        private int _Prod_group_id;
        private string _Prod_group_name;
        private string _Prod_group_remark;
        private int _Prod_group_status;

        [Column(Name = "Prod_group_id",AutoSync=AutoSync.OnInsert,DbType="Int NOT NULL IDENTITY", IsPrimaryKey = true,IsDbGenerated=true)]
        public int Prod_group_id
        {
            get
            {
                return _Prod_group_id;
            }
            set
            {
                _Prod_group_id = value;
            }
        }
        [Column(Name = "Prod_group_name")]
        public string Prod_group_name
        {
            get
            {
                return _Prod_group_name;
            }
            set
            {
                _Prod_group_name = value;
            }
        }
        [Column(Name = "Prod_group_remark")]
        public string Prod_group_remark
        {
            get
            {
                return _Prod_group_remark;
            }
            set
            {
                _Prod_group_remark = value;
            }
        }
        [Column(Name = "Prod_group_status ")]
        public int Prod_group_status {
            get { return _Prod_group_status;
            }
            set { _Prod_group_status = value;
            }
        }
    }


    -----------------------------------------


     and how to syntex on query Thank you.
    Saturday, December 26, 2009 9:42 AM

Answers

  • hey chid,

    i am assuming you already have these tables defined in your data context.  since product is part of product group, i am assuming there is PK/FK relationship.  If so, you would have to define the association. product group would contain the EntitySet and product would contain the EntityRef.

    Product Group Table

     

    private EntityRef<Product_Group> _Group;
    [Association(Name="Key", Storage="_ProductGroup", ThisKey="Prod_Group_ID", OtherKey="Prod_Group_ID")]
    public EntityRef<Product_Group> Group
    {
    get
    {
    return this._Group.Entity;
    }
    set
    {
    this._Group.Entity = value;
    }
    }

    Product Table

    EntitySet<Product_T> _Products;
    [Association(Name="Key", Storage="_Product_T", ThisKey="Prod_Group_ID", OtherKey="Prod_Group_ID")]
    public EntitySet<Client_CustomerValue> Products
    {
    get
    {
    return this._Products;
    }
    set
    {
    this._Products.Assign(value);
    }
    }

    If you want to use "join" without PK/FK relationship, you can use the following approach.

    Table
    <ProductGroup> fTable = context.GetTable<ProductGroup>();
    Table<Product_T> sTable = context.GetTable<Product_T>();
    var
    query = fTable.Join(sTable,
                                   productGroup => productGroup,
                                   product => product.Prod_Group_ID,
                                  (productGroup, product) =>
    new { Gname = productGroup.Prod_Group_Name, Pname = product.Product_Name });

    foreach (var obj in query)
        {
            Console.WriteLine(
                "{0} - {1}",
                obj.Prod_Group_Name,
                obj.Product_Name);
        }

    regards,


    kashif
    Saturday, December 26, 2009 1:10 PM