none
LINQ to SQL RRS feed

  • Question

  • Hi, condition: sql database customs with table dbo.goods

    create table  dbo.goods (calendar datetime,good_type varchar(max),good_amount int)
    insert dbo.goods 
    values ('2020-11-01T07:48:15.000', 'peach: invoice 715282; 15 pallets per 1832 boxes', null),
    ('2020-12-01T18:25:14.000', 'apple green, specification 2981, 185 boxes; apple red: bill 133454, 1334 
    pallets per 12453 boxes, 18 pallets - 134 boxes on pallet',null),
    ('2020-11-02T11:15:34.000', 'brush. invoice 13353; 153843 items', 153843)

    I need to calculate good_amount, where it's null. Result will be: peach - 1832,

    apple- 12772

    Thank you

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Data.Linq;
    using System.Data.Linq.Mapping;
    
    namespace LinqDataManipulationApp
    {
        public class Customs : DataContext
        {
            
            public Table<Good> goods;
            
    
            public Customs(string 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;
                }
            }
            
        }
        class Program
        {
    
            static void Main(string[] args)
            {
                Customs db = new Customs(@"c:\linqtest6\customs.mdf");
    
    
    
                var goodQuery =
                    from i in db.goods
                    where i.good_type.Contains( "123456789")
                    select i;
    
                foreach (Good i in goodQuery)
                {
                    
                    Console.WriteLine(i.calendar+" "+i.good_type+" " i.good_amount);
                    Console.ReadLine();
                }
            }
        }
    }
    

    Wednesday, February 5, 2020 7:05 PM

Answers

  • If you want to extract and sum certain numbers, then try something like this:

     

    var selected_goods =

           db.goods.ToList( );

     

    foreach( var g in selected_goods )

    {

           g.good_amount =

                  Regex.Matches( g.good_type, @"(?i)\d+(?=\s+(boxes|items))" )

                  .Cast<Match>( )

                  .Sum( m => int.Parse( m.Value ) );

    }

    • Marked as answer by Decompressor Friday, February 7, 2020 4:59 PM
    Thursday, February 6, 2020 6:13 AM

All replies

  • Here is how to replace a null value with a value of your choice.

    using (CustomContext context = new CustomContext())
    {
        var results = context.goods.Select(item => item.good_amount ?? 1832).Sum(item => item);
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, February 5, 2020 8:00 PM
    Moderator
  • If you want to extract and sum certain numbers, then try something like this:

     

    var selected_goods =

           db.goods.ToList( );

     

    foreach( var g in selected_goods )

    {

           g.good_amount =

                  Regex.Matches( g.good_type, @"(?i)\d+(?=\s+(boxes|items))" )

                  .Cast<Match>( )

                  .Sum( m => int.Parse( m.Value ) );

    }

    • Marked as answer by Decompressor Friday, February 7, 2020 4:59 PM
    Thursday, February 6, 2020 6:13 AM
  • Here is how to replace a null value with a value of your choice.

    using (CustomContext context = new CustomContext())
    {
        var results = context.goods.Select(item => item.good_amount ?? 1832).Sum(item => item);
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Data.Linq;
    using System.Data.Linq.Mapping;
    using System.Text.RegularExpressions;
    using FluentValidation.Validators;
    
    namespace LinqDataManipulationApp
    {
        public class Customs : DataContext
        {
            
            public Table<Good> goods;
            
    
            public Customs(string 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;
                }
            }
           
            
        }
        class Program
        {
    
            static void Main(string[] args)
            {
                Customs db = new Customs(@"c:\linqtest6\customs.mdf");
    
                using (CustomContext context = new CustomContext())
                {
                    var results = context.goods.Select(item => item.good_amount ?? 1832).Sum(item => item);
                }
    
    
    
                var selected_goods =
    
            db.goods.ToList();
    
    
    
                foreach (var g in selected_goods)
    
                {
    
                    g.good_amount =
    
                           Regex.Matches(g.good_type, @"(?i)\d+(?=\s+(boxes|items))")
    
                           .Cast<Match>()
    
                           .Sum(m => int.Parse(m.Value));
                   
    
                }
    
                
            }
        }
    }
    
    Severity Code Description Project File Line Suppression State
    Error CS1674 'CustomContext': type used in a using statement must be implicitly convertible to 'System.IDisposable' or implement a suitable 'Dispose' method. LinqDataManipulationApp
    Error CS7036 There is no argument given that corresponds to the required formal parameter 'context' of 'CustomContext.CustomContext(PropertyValidatorContext)' LinqDataManipulationApp
    Error CS1061 'CustomContext' does not contain a definition for 'goods' and no accessible extension method 'goods' accepting a first argument of type 'CustomContext' could be found (are you missing a using directive or an assembly reference?) LinqDataManipulationApp

    Thursday, February 6, 2020 8:28 AM
  • Severity Code

    Description Project File Line Suppression State
    Error CS1674 'CustomContext': type used in a using statement must be implicitly convertible to 'System.IDisposable' or implement a suitable 'Dispose' method. LinqDataManipulationApp
    Error CS7036 There is no argument given that corresponds to the required formal parameter 'context' of 'CustomContext.CustomContext(PropertyValidatorContext)' LinqDataManipulationApp
    Error CS1061 'CustomContext' does not contain a definition for 'goods' and no accessible extension method 'goods' accepting a first argument of type 'CustomContext' could be found (are you missing a using directive or an assembly reference?) LinqDataManipulationApp

    The focus should be on the following to test for null and not the other code.

    item.good_amount ?? 1832

    While the rest comes from me writing the code sample with Entity Framework Code First.

    And on a side note all tables should have a primary key which your table does not, I would encourage you to add a auto incrementing primary key. 


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, February 6, 2020 11:08 AM
    Moderator
  • There is one problem else: when I press "start", I receive error: "The null value cannot be assigned to a member with type System.Int32 which is a non-nullable value type." How to solve this?
    Thursday, February 6, 2020 12:20 PM
  • There is one problem else: when I press "start", I receive error: "The null value cannot be assigned to a member with type System.Int32 which is a non-nullable value type." How to solve this?

    Cast it to a nullable int

    (int?)item.good_amount


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, February 6, 2020 1:01 PM
    Moderator
  • There is one problem else: when I press "start", I receive error: "The null value cannot be assigned to a member with type System.Int32 which is a non-nullable value type." How to solve this?

    Cast it to a nullable int

    (int?)item.good_amount


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    How to do this?

    Thursday, February 6, 2020 2:47 PM
  • Let's try a different way (not sure why what I gave you failed as I tested in and worked on my computer)

    var results = context.goods
        .Select(item => item.good_amount.HasValue ? 
            item.good_amount.Value : 
            153843)
        .ToList()
        .Sum(item => item);
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, February 6, 2020 3:26 PM
    Moderator
  • Let's try a different way (not sure why what I gave you failed as I tested in and worked on my computer)

    var results = context.goods
        .Select(item => item.good_amount.HasValue ? 
            item.good_amount.Value : 
            153843)
        .ToList()
        .Sum(item => item);


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Data.Linq;
    using System.Data.Linq.Mapping;
    using System.Text.RegularExpressions;
    using FluentValidation.Validators;
    
    namespace LinqDataManipulationApp
    {
        public class Customs : DataContext
        {
            
            public Table<Good> goods;
            
    
            public Customs(string 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;
                }
                
            }
            
           
            
        }
        class Program
        {
    
            static void Main(string[] args)
            {
                Customs db = new Customs(@"c:\linqtest6\customs.mdf");
    
                using (var context = new CustomContext())
                {
                    var results = context.goods
                    .Select(item => item.good_amount.HasValue ?
                    item.good_amount.Value : 153843)
                    .ToList()
                     .Sum(item => item);
                }
    
    
    
    
                
    
                var selected_goods = db.goods.ToList();
                
    
    
                foreach (var g in selected_goods)
    
                {
                                       g.good_amount =
    
                               Regex.Matches(g.good_type, @"(?i)\d+(?=\s+(boxes|items))")
    
                               .Cast<Match>()
    
                               .Sum(m => int.Parse(m.Value));
                    
                   
    
                }
    
                
            }
        }
    }rror CS1674
    'CustomContext': type used in a using statement must be implicitly convertible to 'System.IDisposable' or implement a suitable 'Dispose' method.
    LinqDataManipulationApp 
    Error CS7036 There is no argument given that corresponds to the required formal parameter 'context' of 'CustomContext.CustomContext(PropertyValidatorContext)' LinqDataManipulationApp
    Error CS1061 'CustomContext' does not contain a definition for 'goods' and no accessible extension method 'goods' accepting a first argument of type 'CustomContext' could be found (are you missing a using directive or an assembly reference?) LinqDataManipulationApp
    Thursday, February 6, 2020 3:41 PM
  • Unless I have you database and project we can go at this all day long. 

    To repeat myself, I did this with Entity Framework 6, code first with your database table with one alteration of adding a primary key which will not cause the current issue.

    Here is the DbContext

    namespace WindowsFormsApp6
    {
        using System;
        using System.Data.Entity;
        using System.ComponentModel.DataAnnotations.Schema;
        using System.Linq;
    
        public partial class CustomContext : DbContext
        {
            public CustomContext()
                : base("name=CustomContext")
            {
            }
    
            public virtual DbSet<good> goods { get; set; }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Entity<good>()
                    .Property(e => e.good_type)
                    .IsUnicode(false);
            }
        }
    }
    

    The model

    namespace WindowsFormsApp
    {
        using System;
        using System.Collections.Generic;
        using System.ComponentModel.DataAnnotations;
        using System.ComponentModel.DataAnnotations.Schema;
        using System.Data.Entity.Spatial;
    
        public partial class good
        {
            private int? _goodAmount; 
            public int id { get; set; }
    
            public DateTime? calendar { get; set; }
    
            public string good_type { get; set; }
    
            public int? good_amount
            {
                get => _goodAmount;
                set => _goodAmount = value;
            }
        }
    }
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, February 6, 2020 4:17 PM
    Moderator
  • Unless I have you database and project we can go at this all day long. 

    To repeat myself, I did this with Entity Framework 6, code first with your database table with one alteration of adding a primary key which will not cause the current issue.

    Here is the DbContext

    namespace WindowsFormsApp6
    {
        using System;
        using System.Data.Entity;
        using System.ComponentModel.DataAnnotations.Schema;
        using System.Linq;
    
        public partial class CustomContext : DbContext
        {
            public CustomContext()
                : base("name=CustomContext")
            {
            }
    
            public virtual DbSet<good> goods { get; set; }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Entity<good>()
                    .Property(e => e.good_type)
                    .IsUnicode(false);
            }
        }
    }

    The model

    namespace WindowsFormsApp
    {
        using System;
        using System.Collections.Generic;
        using System.ComponentModel.DataAnnotations;
        using System.ComponentModel.DataAnnotations.Schema;
        using System.Data.Entity.Spatial;
    
        public partial class good
        {
            private int? _goodAmount; 
            public int id { get; set; }
    
            public DateTime? calendar { get; set; }
    
            public string good_type { get; set; }
    
            public int? good_amount
            {
                get => _goodAmount;
                set => _goodAmount = value;
            }
        }
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Is it possible to make connection to database directly on sql server, not copy database to some folder? Other words, connection string not @"c:\linqtest6\customs.mdf", but "Data source=.... Integrated security=true;"? Irritating to copy each time updated database to folder...
    Thursday, February 6, 2020 4:59 PM
  • So I added primary key id...
    Thursday, February 6, 2020 5:09 PM
  • using System.Data.Entity.Spatial; error and I can't add reference. I have no such reference in  reference manager assemblies. .Net Framework 4.7.2
    Thursday, February 6, 2020 5:57 PM
  • using System;
    using System.Linq;
    using System.Data.Linq;
    using System.Data.Linq.Mapping;
    using System.Text.RegularExpressions;
    
    
    namespace LinqDataManipulationApp
    {    
        
        public class Customs : DataContext
        {
            
            public Table<Good> goods;
            
    
            public Customs(string 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; set; }
            
    
    
        }
        class Program
        {
    
            static void Main(string[] args)
            {
                Customs db = new Customs(@"c:\linqtest6\customs.mdf");
    
                      
    
                
    
                var selected_goods = db.goods.ToList();
                
    
    
                foreach (var g in selected_goods)
    
                {
                                       g.good_amount =
    
                               Regex.Matches(g.good_type, @"(?i)\d+(?=\s+(boxes|items))")
    
                               .Cast<Match>()
    
                               .Sum(m => int.Parse(m.Value));
                    
                   
    
                }
    
                
            }
        }
    }
    
    In such form app compiles, but result is empty command line
    Thursday, February 6, 2020 6:54 PM
  • If you want to extract and sum certain numbers, then try something like this:

     

    var selected_goods =

           db.goods.ToList( );

     

    foreach( var g in selected_goods )

    {

           g.good_amount =

                  Regex.Matches( g.good_type, @"(?i)\d+(?=\s+(boxes|items))" )

                  .Cast<Match>( )

                  .Sum( m => int.Parse( m.Value ) );

    }

    It's really good to extract and sum numbers. I tested it in lists, but as for my table, it doesn't work
    Friday, February 7, 2020 5:02 PM
  • OOOO! I've made some modifications and it works! One more question: how to make queries directly to database on sql server? Is it possible?
    using System;
    using System.Linq;
    using System.Data.Linq;
    using System.Data.Linq.Mapping;
    using System.Text.RegularExpressions;
    
    
    namespace LinqDataManipulationApp
    {    
        
        public class Customs : DataContext
        {
            
            public Table<Good> goods;
            
    
            public Customs(string 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; set; }
            
    
    
        }
        class Program
        {
    
            static void Main(string[] args)
            {
                Customs db = new Customs(@"c:\linqtest6\customs.mdf");
    
                      
    
                
    
                //var selected_goods = db.goods.ToList();
                
    
    
               // foreach (var g in selected_goods)
               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.good_amount);
                    Console.ReadLine();
    
                }
    
                
            }
        }
    }
    

    Friday, February 7, 2020 5:30 PM