none
let clause and join clause together RRS feed

  • Question

  • How to write query correctly to get good_type, calendar from db.goods, per_name, position from db.employees

    and good_amount calculated with let x?

    using System;
    using System.Linq;
    using System.Data.Linq;
    using System.Data.Linq.Mapping;
    using System.Text.RegularExpressions;
    using System.Data.SqlClient;
    using System.Data;
    
    namespace LinqDataManipulationApp
    {    
        
        public class Customs : DataContext
        {
            
            public Table<Good> goods;
            public Table<Employee> employees;
            
    
            //public Customs(string connection) : base(connection) { }
    
            public Customs(IDbConnection connection) : base(connection)
            {
            }
        }
        [Table(Name = "Goods")]
        public class Good
        {
            private DateTime _calendar;
            [Column(Storage = "_calendar")]
            public DateTime calendar
            {
                get
                {
                    return this._calendar;
                }
                set
                {
                    this._calendar = value;
                }
            }
    
    
            private string _good_type;
            [Column(Storage = "_good_type")]
            public string good_type
            {
                get
                {
                    return this._good_type;
                }
                set
                {
                    this._good_type = value;
                }
            }
    
            private int? _good_amount;
            [Column(Storage = "_good_amount")]
            public int? good_amount
    
            {
                get
                {
                    return this._good_amount;
                }
                set
                {
    
                    this._good_amount = value;
                }
    
            }
            private int _id;
            [Column(Storage = "_id")]
            public int id
            {
                get
                {
                    return this._id;
                }
    
                set
                {
                    this._id = value;
                }
            
            }
            private int _enterprise;
            [Column(Storage = "_enterprise")]
            public int enterprise
            {
                get
                {
                    return this._enterprise;
                }
    
                set
                {
                    this._enterprise = value;
                }
    
            }
    
    
        }
        [Table(Name = "Employees")]
        public class Employee
        {
            private int _id;
            private string _per_name;
            private string _per_adress;
            private int _per_id;
            private int _position;
            private int _enterprise;
    
            [Column(Storage="_id")]
            public int id
            {
                get
                {
                    return this._id;
                }
                set
                {
                    this._id = value;
                }
            }
            [Column(Storage ="_per_name")]
            public string per_name
            {
                get
                {
                    return this._per_name;
                }
                set
                {
                    this._per_name = value;
                }
            }
            [Column(Storage="_per_adress")]
            public string per_adress
            {
                get
                {
                    return this._per_adress;
                }
                set
                {
                    this._per_adress = value;
                }
            }
            [Column(Storage ="_per_id")]
            public int per_id
            {
                get
                {
                    return this._per_id;
                }
                set
                {
                    this._per_id = value;
                }
            }
            [Column(Storage = "_position")]
            public int position
            {
                get
                {
                    return this._position;
                }
                set
                {
                    this._position = value;
                }
            }
            [Column(Storage ="_enterprise")]
            public int enterprise
            {
                get
                {
                    return this._enterprise;
                }
                set
                {
                    this._enterprise = value; 
                }
            }
        }
        class Program
        {
    
            static void Main(string[] args)
            {
                string connString = (Properties.Settings.Default.storageConnectionString);
                SqlConnection nwindConn = new SqlConnection(connString);
                nwindConn.Open();
                //DataContext db = new DataContext(Properties.Settings.Default.storageConnectionString);
                // Customs db = new Customs(@"c:\linqtest6\customs.mdf");
                Customs db = new Customs(nwindConn);
    
    
    
                /*foreach (var g in db.goods)
    
                {
                                       g.good_amount =
    
                               Regex.Matches(g.good_type, @"(?i)\d+(?=\s+(boxes|items))")
    
                               .Cast<Match>()
    
                               .Sum(m => int.Parse(m.Value));
    
                    Console.WriteLine(g.id+" "+ g.calendar+" "+g.good_type.Substring(0,6)+" "+g.good_amount+" "+
                        +g.enterprise);*/
                var query = from g in db.goods
                            join o in db.employees on g.enterprise equals o.enterprise
                            let x = Regex.Matches(g.good_type, @"(?i)\d+(?=\s+(boxes|items))")
                                   .Cast<Match>().Sum(m => int.Parse(m.Value))
                            where o.per_name.Contains("Bolton")
                            select x;
                            
                           
                foreach (var z in query)
                {
                    Console.WriteLine(z);
                    Console.ReadLine();
                }   
    
               //}
    
                
            }
        }
    }

    Sunday, February 9, 2020 7:36 PM

All replies

  • Hi pavlob,

    Maybe you can try the keyword "into". Just like the following demo.

        var items = from good in goods
                    join emp in employees on good.enterprise equals emp.enterprise
                            into customerOrders
                    let order = customerOrders.FirstOrDefault()
                    select new { };

    Regards,

    Kyle


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 10, 2020 8:11 AM
    Moderator
  • Hi pavlob,

    Maybe you can try the keyword "into". Just like the following demo.

        var items = from good in goods
                    join emp in employees on good.enterprise equals emp.enterprise
                            into customerOrders
                    let order = customerOrders.FirstOrDefault()
                    select new { };

    Regards,

    Kyle


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    What is customerOrders? Where must I insert:
    Regex.Matches(g.good_type, @"(?i)\d+(?=\s+(boxes|items))")
                                   .Cast<Match>().Sum(m => int.Parse(m.Value)))


    Monday, February 10, 2020 10:33 AM
  • Hi,

    It is a temporary identifier to store the results. For more info, you can refer to into (C# Reference).

    Regards,

    Kyle


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 11, 2020 7:38 AM
    Moderator