none
Linq Sql Server and GUID RRS feed

  • Question

  • I have a SQL SERVER table with column that default value is (newsequentialid()), 

    and the length is 36 for example :

    '83AD0463-8022-EA11-80C8-C81F66D53EF5'

    when I try to quest the table using dbml and linq:

    var res = context.TransactionsLogs.Where(
                t => t.TransactionID.Equals(transactionID) && t.ResponseCode.Equals("000"));

    i see next message :

    the only way it will be 32 digits is with no dashes.

    any idea?

    Thursday, December 19, 2019 6:55 PM

All replies

  • Can we see the class/column mapping definition for the guid column?

    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, December 19, 2019 7:38 PM
    Moderator
  • Sql

    CREATE TABLE [dbo].[TransactionsLog](
    	[TransactionLogID] [int] IDENTITY(1,1) NOT NULL,
    	[TransactionID] [uniqueidentifier] NOT NULL,
    	[CreateDate] [datetime] NOT NULL,
    	[Reponse] [nvarchar](500) NOT NULL,
    	[ResponseCode] [varchar](50) NOT NULL,
     CONSTRAINT [PK_TranzilaTransactionsLog] PRIMARY KEY CLUSTERED 
    (
    	[TransactionLogID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO

    dbml

    public partial class TransactionsLog : INotifyPropertyChanging, INotifyPropertyChanged
    {
    	
    	private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(String.Empty);
    	
    	private int _TransactionLogID;
    	
    	private System.Guid _TransactionID;
    	
    	private System.DateTime _CreateDate;
    	
    	private string _Reponse;
    	
    	private string _ResponseCode;
    	
    	private EntityRef<Transaction> _Transaction;
    	
        #region Extensibility Method Definitions
        partial void OnLoaded();
        partial void OnValidate(System.Data.Linq.ChangeAction action);
        partial void OnCreated();
        partial void OnTransactionLogIDChanging(int value);
        partial void OnTransactionLogIDChanged();
        partial void OnTransactionIDChanging(System.Guid value);
        partial void OnTransactionIDChanged();
        partial void OnCreateDateChanging(System.DateTime value);
        partial void OnCreateDateChanged();
        partial void OnReponseChanging(string value);
        partial void OnReponseChanged();
        partial void OnResponseCodeChanging(string value);
        partial void OnResponseCodeChanged();
        #endregion
    	
    	public TransactionsLog()
    	{
    		this._Transaction = default(EntityRef<Transaction>);
    		OnCreated();
    	}
    	
    	[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_TransactionLogID", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]
    	public int TransactionLogID
    	{
    		get
    		{
    			return this._TransactionLogID;
    		}
    		set
    		{
    			if ((this._TransactionLogID != value))
    			{
    				this.OnTransactionLogIDChanging(value);
    				this.SendPropertyChanging();
    				this._TransactionLogID = value;
    				this.SendPropertyChanged("TransactionLogID");
    				this.OnTransactionLogIDChanged();
    			}
    		}
    	}
    	
    	[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_TransactionID", DbType="UniqueIdentifier NOT NULL")]
    	public System.Guid TransactionID
    	{
    		get
    		{
    			return this._TransactionID;
    		}
    		set
    		{
    			if ((this._TransactionID != value))
    			{
    				if (this._Transaction.HasLoadedOrAssignedValue)
    				{
    					throw new System.Data.Linq.ForeignKeyReferenceAlreadyHasValueException();
    				}
    				this.OnTransactionIDChanging(value);
    				this.SendPropertyChanging();
    				this._TransactionID = value;
    				this.SendPropertyChanged("TransactionID");
    				this.OnTransactionIDChanged();
    			}
    		}
    	}
    	
    	[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_CreateDate", DbType="DateTime NOT NULL")]
    	public System.DateTime CreateDate
    	{
    		get
    		{
    			return this._CreateDate;
    		}
    		set
    		{
    			if ((this._CreateDate != value))
    			{
    				this.OnCreateDateChanging(value);
    				this.SendPropertyChanging();
    				this._CreateDate = value;
    				this.SendPropertyChanged("CreateDate");
    				this.OnCreateDateChanged();
    			}
    		}
    	}
    	
    	[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Reponse", DbType="NVarChar(500) NOT NULL", CanBeNull=false)]
    	public string Reponse
    	{
    		get
    		{
    			return this._Reponse;
    		}
    		set
    		{
    			if ((this._Reponse != value))
    			{
    				this.OnReponseChanging(value);
    				this.SendPropertyChanging();
    				this._Reponse = value;
    				this.SendPropertyChanged("Reponse");
    				this.OnReponseChanged();
    			}
    		}
    	}
    	
    	[global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_ResponseCode", DbType="VarChar(50) NOT NULL", CanBeNull=false)]
    	public string ResponseCode
    	{
    		get
    		{
    			return this._ResponseCode;
    		}
    		set
    		{
    			if ((this._ResponseCode != value))
    			{
    				this.OnResponseCodeChanging(value);
    				this.SendPropertyChanging();
    				this._ResponseCode = value;
    				this.SendPropertyChanged("ResponseCode");
    				this.OnResponseCodeChanged();
    			}
    		}
    	}
    	
    	[global::System.Data.Linq.Mapping.AssociationAttribute(Name="Transaction_TransactionsLog", Storage="_Transaction", ThisKey="TransactionID", OtherKey="TransactionID", IsForeignKey=true)]
    	public Transaction Transaction
    	{
    		get
    		{
    			return this._Transaction.Entity;
    		}
    		set
    		{
    			Transaction previousValue = this._Transaction.Entity;
    			if (((previousValue != value) 
    						|| (this._Transaction.HasLoadedOrAssignedValue == false)))
    			{
    				this.SendPropertyChanging();
    				if ((previousValue != null))
    				{
    					this._Transaction.Entity = null;
    					previousValue.TransactionsLogs.Remove(this);
    				}
    				this._Transaction.Entity = value;
    				if ((value != null))
    				{
    					value.TransactionsLogs.Add(this);
    					this._TransactionID = value.TransactionID;
    				}
    				else
    				{
    					this._TransactionID = default(System.Guid);
    				}
    				this.SendPropertyChanged("Transaction");
    			}
    		}
    	}
    	
    	public event PropertyChangingEventHandler PropertyChanging;
    	
    	public event PropertyChangedEventHandler PropertyChanged;
    	
    	protected virtual void SendPropertyChanging()
    	{
    		if ((this.PropertyChanging != null))
    		{
    			this.PropertyChanging(this, emptyChangingEventArgs);
    		}
    	}
    	
    	protected virtual void SendPropertyChanged(String propertyName)
    	{
    		if ((this.PropertyChanged != null))
    		{
    			this.PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
    		}
    	}
    }

    Thursday, December 19, 2019 8:16 PM
  • There is no default constraint on this column as you described. 

    Also note that applying a default constraint after the table is created does not do anything with existing rows. Ensure that all the existing rows have a valid GUID in them otherwise the call will fail if a bad row is returned from the query.


    Michael Taylor http://www.michaeltaylorp3.net

    Thursday, December 19, 2019 8:37 PM
    Moderator
  • please ignore " with column that default value is (newsequentialid())," this is on the other table.

    the table that I want to query and gives me an error has a 

    private System.Guid _TransactionID;

    which causes the problem

    Thursday, December 19, 2019 8:49 PM
  • Seems like this would be more inline with a guid

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public System.Guid TransactionID

    Or when I scaffold your table Entity Framework does this

    using System;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    
    namespace NorthWind.Data.Models
    {
        public partial class TransactionsLog
        {
            [Column("TransactionLogID")]
            public int TransactionLogId { get; set; }
            [Column("TransactionID")]
            public Guid TransactionId { get; set; }
            [Column(TypeName = "datetime")]
            public DateTime CreateDate { get; set; }
            [Required]
            [StringLength(500)]
            public string Reponse { get; set; }
            [Required]
            [StringLength(50)]
            public string ResponseCode { get; set; }
        }
    }
    


    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, December 19, 2019 8:51 PM
    Moderator
  • What? Please post the actual query that is causing the error then. The query you posted would require that TransactionID on TransactionsLog in the DB be a GUID. The private field you mention isn't relevant for a query to the DB as the DB won't use this field.

    Michael Taylor http://www.michaeltaylorp3.net

    Thursday, December 19, 2019 8:58 PM
    Moderator
  • @Kareninstructor

    not clear to me what you wrote can you explain?

    Thursday, December 19, 2019 9:01 PM
  • @Kareninstructor

    not clear to me what you wrote can you explain?

    I scaffold your table meaning reverse engineering from database table to concrete class that can be used by Entity Framework Core. Similarly this can be done with Entity Framework 6 in Visual Studio while scaffolding is best done thru a tool or Visual Studio extension (here is one) or via T4 templating

    Once reverse engineering is done the right data type is annotated for each property with options to tweak in the data context on model create.


    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, December 19, 2019 10:00 PM
    Moderator