subsonic / SubSonic-3.0

3.0.0.3 SimpleRepository issue with MySQL Server 5.1 and .Net Connector 6.0.4

var p = repo.GetPaged(index, pagesize)

Throws a MySQL syntax exception when run. After doing some digging (getting the logs to work) this is the query that is being executed:

SELECT COUNT(``id``) AS `CountOfid` FROM Sales; //Does not work

SELECT COUNT(id) AS CountOfid FROM Sales; //Ran this to make sure it wasn't an issue in the DB and it runs like a champ.

I have this simple little method that is trying to use the SimpleRepositry Find method in SubSonic 3.0.0.3. When I make the call I get a invalid operation exception "Lambda Parameter not in scope".
To reproduce this fault I used the following code :

class SimpleCustomer

{
    /// <summary>
    /// Gets or sets the customer GUID.
    /// </summary>
    /// <value>The customer GUID.</value>
    public Guid CustomerGUID { get; set; }

    /// <summary>
    /// Gets or sets the ID tag number that is assigned to the customer
    /// </summary>
    /// <value>The ID.</value>
    public string ID { get; set; }

    public bool Active { get; set; }

    /// <summary>
    /// Gets or sets the first names.
    /// </summary>
    /// <value>The first names.</value>
    public string FirstNames { get; set; }
    /// <summary>
    /// Gets or sets the last names.
    /// </summary>
    /// <value>The last names.</value>
    public string LastNames { get; set; }
}

    public static void AddSimpleCustomer()
    {
        SimpleRepository repos = new SimpleRepository("EmptyDB", SimpleRepositoryOptions.RunMigrations);

        string searchTerm = "jon";
        string id = "00033221100";
        SimpleCustomer x = new SimpleCustomer();

        if (repos.Exists<SimpleCustomer>(c => c.ID == id) == false)
        {
            x.FirstNames = "John";
            x.LastNames = "Smith";
            x.ID = id;
            x.CustomerGUID = Guid.NewGuid();
            repos.Add<SimpleCustomer>(x);
        }
        // Boom - Thinks go horribly wrong here
        SimpleCustomer y = repos.Find<SimpleCustomer>(c => c.FirstNames.StartsWith(searchTerm)).FirstOrDefault();
        //Or Here
        y = repos.Find<SimpleCustomer>(c => c.FirstNames.Contains(searchTerm)).FirstOrDefault();

    }

I can work around this issue using

public override IEnumerable FindCustomers(string searchTerm)

    {            

        return Repos.All<Customer>().Where(cust => cust.FirstNames.Contains(searchTerm) || cust.LastNames.Contains(searchTerm)).ToArray();
    }

But given that it is using the All method, I suspect that this may not scale well :-)

ActiveRecord attempts to update computed columns in sql server. This effectively means that any table with a computed column can not be used.

These two commits add awareness of Computed columns, to the templates and the core.

• Computed columns can still be updated in code
• Core will not try to update/insert a column that is computed

In the future, it should make computed column property "protected set" , and not add them to the dirty columns collection.

http://github.com/awbacker/SubSonic-3.0/commit/e8141cffb4c5a6c22269f402c7e08dcfc8ff6c23
http://github.com/awbacker/SubSonic-3.0-Templates/commit/d32b69f8f4a484c77567207a071f0caa46038701

Sorry about the CS files in there, don't know what to do bout that since I am so new to GIT.

There are some issues with the current trunk and invoking the datacontext.[Tables]. Count() or .Where(). I tried testing this today. My code is using trunk/master subsonic and active record templates from today (8/7/2009 @ 6PM CST)

Saving, getting, and deleting a single row worked fine in MediumTrust. When I tried to do a DB.Table.Where(o=> expression).ToList(); I got the SecurityException below. (this also happened when trying to pull all rows with a simple LINQ query. (from row in DB.Tables select row);

I don't have any suggestions yet. I believe this is happening on a Compile() function; possibly where the expression tree is constructed??? I haven't dug into it yet.

Error: System.MethodAccessException: System.Runtime.CompilerServices.StrongBox1..ctor(System.__Canon) ---> System.Security.SecurityException: Request failed.<br/> at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)<br/> at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Object assemblyOrString, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)<br/> at System.Security.CodeAccessSecurityEngine.CheckSetHelper(PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle rmh, Object assemblyOrString, SecurityAction action, Boolean throwException)<br/> at System.Security.PermissionSetTriple.CheckSetDemand(PermissionSet demandSet, PermissionSet& alteredDemandset, RuntimeMethodHandle rmh)<br/> at System.Security.PermissionListSet.CheckSetDemand(PermissionSet pset, RuntimeMethodHandle rmh)<br/> at System.Security.PermissionListSet.DemandFlagsOrGrantSet(Int32 flags, PermissionSet grantSet)<br/> at System.Threading.CompressedStack.DemandFlagsOrGrantSet(Int32 flags, PermissionSet grantSet)<br/> at System.Security.CodeAccessSecurityEngine.ReflectionTargetDemandHelper(Int32 permission, PermissionSet targetGrant, CompressedStack securityContext)<br/> at System.Security.CodeAccessSecurityEngine.ReflectionTargetDemandHelper(Int32 permission, PermissionSet targetGrant)<br/> The action that failed was:<br/> Demand<br/> The type of the first permission that failed was:<br/> System.Security.PermissionSet<br/> --- End of inner exception stack trace --- at System.Reflection.MethodBase.PerformSecurityCheck(Object obj, RuntimeMethodHandle method, IntPtr parent, UInt32 invocationFlags)<br/> at System.Reflection.RuntimeConstructorInfo.Invoke(BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)<br/> at System.RuntimeType.CreateInstanceImpl(BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes)<br/> at System.Activator.CreateInstance(Type type, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes)<br/> at System.Linq.Expressions.ExpressionCompiler.AddGlobal(Type type, Object value)<br/> at System.Linq.Expressions.ExpressionCompiler.GenerateConstant(ILGenerator gen, Type type, Object value, StackType ask)<br/> at System.Linq.Expressions.ExpressionCompiler.GenerateConstant(ILGenerator gen, ConstantExpression c, StackType ask)<br/> at System.Linq.Expressions.ExpressionCompiler.Generate(ILGenerator gen, Expression node, StackType ask)<br/> at System.Linq.Expressions.ExpressionCompiler.GenerateArgs(ILGenerator gen, ParameterInfo[] pis, ReadOnlyCollection1 args)
at System.Linq.Expressions.ExpressionCompiler.GenerateMethodCall(ILGenerator gen, MethodInfo mi, ReadOnlyCollection1 args, Type objectType)<br/> at System.Linq.Expressions.ExpressionCompiler.GenerateMethodCall(ILGenerator gen, MethodCallExpression mc, StackType ask)<br/> at System.Linq.Expressions.ExpressionCompiler.Generate(ILGenerator gen, Expression node, StackType ask)<br/> at System.Linq.Expressions.ExpressionCompiler.GenerateConvert(ILGenerator gen, UnaryExpression u)<br/> at System.Linq.Expressions.ExpressionCompiler.Generate(ILGenerator gen, Expression node, StackType ask)<br/> at System.Linq.Expressions.ExpressionCompiler.GenerateConditional(ILGenerator gen, ConditionalExpression b)<br/> at System.Linq.Expressions.ExpressionCompiler.Generate(ILGenerator gen, Expression node, StackType ask)<br/> at System.Linq.Expressions.ExpressionCompiler.GenerateMemberAssignment(ILGenerator gen, MemberAssignment binding, Type objectType)<br/> at System.Linq.Expressions.ExpressionCompiler.GenerateBinding(ILGenerator gen, MemberBinding binding, Type objectType)<br/> at System.Linq.Expressions.ExpressionCompiler.GenerateMemberInit(ILGenerator gen, ReadOnlyCollection1 bindings, Boolean keepOnStack, Type objectType)
at System.Linq.Expressions.ExpressionCompiler.GenerateMemberInit(ILGenerator gen, MemberInitExpression init)
at System.Linq.Expressions.ExpressionCompiler.Generate(ILGenerator gen, Expression node, StackType ask)
at System.Linq.Expressions.ExpressionCompiler.GenerateLambda(LambdaExpression lambda)
at System.Linq.Expressions.ExpressionCompiler.GenerateCreateDelegate(ILGenerator gen, LambdaExpression lambda)
at System.Linq.Expressions.ExpressionCompiler.Generate(ILGenerator gen, Expression node, StackType ask)
at System.Linq.Expressions.ExpressionCompiler.GenerateArgs(ILGenerator gen, ParameterInfo[] pis, ReadOnlyCollection1 args)<br/> at System.Linq.Expressions.ExpressionCompiler.GenerateNew(ILGenerator gen, NewExpression nex, StackType ask)<br/> at System.Linq.Expressions.ExpressionCompiler.Generate(ILGenerator gen, Expression node, StackType ask)<br/> at System.Linq.Expressions.ExpressionCompiler.GenerateArgs(ILGenerator gen, ParameterInfo[] pis, ReadOnlyCollection1 args)
at System.Linq.Expressions.ExpressionCompiler.GenerateMethodCall(ILGenerator gen, MethodInfo mi, ReadOnlyCollection1 args, Type objectType)<br/> at System.Linq.Expressions.ExpressionCompiler.GenerateMethodCall(ILGenerator gen, MethodCallExpression mc, StackType ask)<br/> at System.Linq.Expressions.ExpressionCompiler.Generate(ILGenerator gen, Expression node, StackType ask)<br/> at System.Linq.Expressions.ExpressionCompiler.GenerateArgs(ILGenerator gen, ParameterInfo[] pis, ReadOnlyCollection1 args)
at System.Linq.Expressions.ExpressionCompiler.GenerateMethodCall(ILGenerator gen, MethodInfo mi, ReadOnlyCollection1 args, Type objectType)<br/> at System.Linq.Expressions.ExpressionCompiler.GenerateMethodCall(ILGenerator gen, MethodCallExpression mc, StackType ask)<br/> at System.Linq.Expressions.ExpressionCompiler.Generate(ILGenerator gen, Expression node, StackType ask)<br/> at System.Linq.Expressions.ExpressionCompiler.GenerateConvert(ILGenerator gen, UnaryExpression u)<br/> at System.Linq.Expressions.ExpressionCompiler.Generate(ILGenerator gen, Expression node, StackType ask)<br/> at System.Linq.Expressions.ExpressionCompiler.GenerateLambda(LambdaExpression lambda)<br/> at System.Linq.Expressions.ExpressionCompiler.CompileDynamicLambda(LambdaExpression lambda)<br/> at System.Linq.Expressions.Expression1.Compile()
at SubSonic.Linq.Structure.DbQueryProvider.Execute(Expression expression)
at SubSonic.Linq.Structure.QueryProvider.System.Linq.IQueryProvider.Execute(Expression expression)
at SubSonic.Linq.Structure.Query1.GetEnumerator()<br/> at System.Collections.Generic.List1..ctor(IEnumerable1 collection)<br/> at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source)

Steps to reproduce:

1. Using SimpleRository and SQL server as you backend, create a C# class and run a Subsonic migration. This will create the corresponding table on the SQL server
   
2. Add a non-nullable Boolean field to the C# class and run migration again

Expected behavior

You expect a new Bit field to be added to the SQL table and also all the existing records to be updated with the value '0' in the new field.

Observed behavior

The new field is not created and the following error is thrown:

Message: The name "False" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted. Source: .Net SqlClient Data Provider Stack trace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at SubSonic.Query.BatchQuery.ExecuteTransaction() in F:\Users\Jericho\Documents\Visual Studio 2008\Projects\SubSonic 3 Source\SubSonic-3.0\SubSonic.Core\Query\BatchQuery.cs:line 198

Cause of the problem

On line 220 in ANSISchemaGenerator.cs, the default value is set to the boolean value "false":

column.DefaultSetting = false;

The result is the following query (notice the default value is set to 'False'):

ALTER TABLE [MyTests] ADD MyBooleanTestColumn bit NOT NULL CONSTRAINT DF_Visits_MyBooleanTestColumn DEFAULT (False)

However, the underlying SQL table field is a 'bit' and therefore the accepted values are 0 or 1.

Solution

Change line 220 in Sql2005Schema.cs from:

column.DefaultSetting = false;

To:

column.DefaultSetting = 0;

I am using activerecord (last subsonic source, with MySQL) and my table name is Payment. SubSonic generates a Payment class. Great! Almost everything works. One exception is SubSonic.Query.Insert, who calls the ToSchemaTable (there is a call to MakePlural there). As result, a obtain a wrong sql command, with Payments.

How a im doing:

SubSonic.Query.BatchQuery batch = new BatchQuery();

    var query = new SubSonic.Query.Insert(provider).Into<Payment>(new String[] { "Number", "Document", "ServiceNumber", "PaymentDate", "FreeDate" })
.Values(new Object[] { payment.Number, payment.Document,
payment.ServiceNumber, payment.PaymentDate.ToString("yyyy-MM-dd HH:mm:ss"),
payment.FreeDate.Value.ToString("yyyy-MM-dd HH:mm:ss") });
    batch.Queue(query);
    batch.Execute();

When I call Find() on a column that allows nulls, SubSonic is generating incorrect SQL. This happens only when I call Find() from a VB.NET project (SubSonic + templates are in a separate C# project in the same solution). If I call Find() from a C# project, there is no problem.

Details:

• latest SubSonic 3 (master branch, dated 2009-07-27) and ActiveRecord
• MS SQL Server 2005 Express Edition.
• Visual Studio 2008 solution
  • SubSonic and T4 templates in a C# class library project
  • My VB application in a separate project referencing the SubSonic project

Here's my table:

CREATE TABLE [dbo].[Fruit](
    [FruitID] [int] NOT NULL,
    [ColorID] [int] NULL,
 CONSTRAINT [PK_Fruit] PRIMARY KEY CLUSTERED 
 ...

In my VB code, I have

Dim bar = Fruit.Find(Function(f As Fruit) f.ColorID = 1)

SubSonic generates the following SQL

SELECT [t0].[ColorID], [t0].[FruitID]
FROM [dbo].[Fruit] AS t0
WHERE ([t0].[ColorID] = 1) <> 0

which fails to execute in DBDataProvider.ExecuteReader with

SqlException was unhandled by user code
Incorrect syntax near '<'

If I make the ColorID column "NOT NULL", the query generates correctly as:

SELECT [t0].[ColorID], [t0].[FruitID]
FROM [dbo].[Fruit] AS t0
WHERE ([t0].[ColorID] = 1)

If I execute the following equivalent Find() in C#, I also have no problems:

var foo = FooDB.Fruit.Find(x => x.ColorID == 1);

Please let me know if you need more information. If there are any workarounds that I can use until this makes it in, I'd appreciate it.

Using 3.0.0.3 and also tried w/ rebuild from the latest @ github
Replicated by the following code in MySql and Advanced Templates

EDIT: Appears to be solved by the solution presented by kamsar
http://github.com/subsonic/SubSonic-3.0/issues#issue/69

Dim v2Context = New SS.v2DB
Dim repo = New SubSonic.Repository.SubSonicRepository(Of SS.administrator)(v2Context)
For i As Integer = 1 To 1000

    Dim admin = New SS.administrator
    admin.Username = Guid.NewGuid.ToString
    admin.Password = Guid.NewGuid.ToString
    admin.LastLogin = Now

    repo.Add(admin)

Next

MySql Query Log Shows:

       62 Connect   someuser@localhost on v2
       62 Query SHOW VARIABLES
       62 Query SHOW COLLATION
       62 Query SET character_set_results=NULL
       62 Init DB   v2
       62 Query INSERT INTO `administrator`(`Username`,`Password`,`LastLogin`)
        VALUES ('a70fd381-e436-449a-96b3-afc3bb1d7481','2f8696c9-fb09-4cf5-b515-fc9b24ded7e7','2009-08-15 21:55:02');SELECT LAST_INSERT_ID() as newid

And so on for each insert until the connections pile up and choke the pool causing a timeout.

Same thing happens if using ActiveRecord templates.

    For i As Integer = 1 To 5
        Dim a As New AR.administrator
        a.Username = Guid.NewGuid.ToString
        a.Password = Guid.NewGuid.ToString
        a.LastLogin = Now
        a.Save()
    Next i

However, performing an insert using the code below properly opens/closes the connection:

    Dim b = New SubSonic.Query.BatchQuery(v2Context.DataProvider)

    For i As Integer = 1 To 1000
        Dim ins = v2Context.Insert.Into(Of SS.administrator)( _
            Function(x) x.Username, _
            Function(x) x.Password, _
            Function(x) x.LastLogin) _
            .Values(Guid.NewGuid.ToString, Guid.NewGuid.ToString, Now.ToString("yyyy-MM-dd"))

        b.QueueForTransaction(ins)

    Next

    b.ExecuteTransaction()

In the current master branch (dated 2009-07-27), boolean values values always return false. This does NOT occur with the SubSonic 3.0.0.3.

Details:

• latest SubSonic 3 (master branch, dated 2009-07-27) and ActiveRecord
• MS SQL Server 2005 Express Edition
• Occurs in C# and VB

Here's my table:

CREATE TABLE [dbo].[Fruit](
    [FruitID] [int] NOT NULL,
    [IsAwesome] [bit] NOT NULL,
 CONSTRAINT [PK_Fruit] PRIMARY KEY CLUSTERED 
(
...

and my table data:

(FruitID, IsAwesome)
(1, True)
(2, False)

Here's my code:

        string s = "";
        var list = FooDB.Fruit.All();
        foreach (var f in list)
        {
            s = s + f.IsAwesome + " ";
        }
        MessageBox.Show(s);

When I execute this with the current master branch of SubSonic, I get:

False False

When I execute this with 3.0.0.3

True False

I have a column in SQL Server 2005 table of type varbinary(MAX). Generated active record class has the corresponding property type byte[]. I can successfully new-up the active record class representing the table, initialize values and save. The static method Find of the active record class returns the full set of data. When using the SubSonic.Linq.Structure.Query<T> then the byte[] field is always null.

In other words

var query = new Query<MyTable>();
var result = (from x in query select x).ToList();

if MyTable has a property of type byte[] (varbinary(MAX) in SQL Server 2005) then that property is always null in the result.

When querying using a GUID, the following error is returned:

The member 'xxx' is not supported. (xxx is the property name)

Example code:
Guid myId = new Guid("EE4FB3A9-2039-40FC-8241-F97967CF38A1");
IList test = Document.Find(x => x.xxx == myId);

I have various columns that aren't loading into my objects. None of the columns that are affected by naming changes resulting from my modified CleanUp method are being loaded. In this function, I'm just replacing underscores with string.empty and fixing the capitalization back like SubSonic 2.x, so "my_table" becomes "MyTable". The fields that weren't affected (other than capitalization changes) load fine.

Hello, I have a table called QuizItem that have these fields

id As Int
created As DateTime
header As Sring
body As String
fk_parent As int
url As String

All the items without parent key would be the question, and ones that have the parent key would be the answer. I am having the problem to get all the latest active questions (based both on questions created time and and answer created time).

I have used the following approach to do the query but keep getting the error or wrong result( answer and questions in diffrent group), is there some issue with the subsonic vb template, or my code is wrong, or sth else, any one can help me please.

From q In Quiz.Data.QuizItem.All Group q By masterID = If(q.FK_Parent Is Nothing, q.ID, q.ID) Into g = Group _

                           Order By g.Max(Function(q2) q2.Created) Descending Select New With {.masterItem = (From q3 In g Where q3.FK_Parent Is Nothing Select q3).Single}

Any idea is appreciated

public IQueryable Find(Expression<Func<T, bool>> expression)

in TestRepository returns all records, making unit tests which rely on the Active Record Testing using Find or SingleOrDefault fail.

What lead me down this road was the BOOL/TINYINT/SByte problem mentioned in issue #Num: #95 Figuring out the same as defacer.

After some digging around I find this: http://lists.mysql.com/commits/79014

And decide to try update MySQL.Data, I was using 5.2.5 iirc. I tried only 5.2.7 and 5.1.7 (and 5.2.5).
I got the same TINYINT -> SByte problem in all but 5.1.7. In the latter I now figure out that the ParameterMarker is ? and DbDataProvider.cs:104 ExecuteReader(QueryCommand qry) throws on /SubSonic.Core/DataProviders/DbDataProvider.cs#L130. saying "Column 'XYZ' cannot be null", even if parameters are added to the command. I found http://forums.mysql.com/read.php?38,200568,200573#msg-200573 and added "old syntax=true" in my connection string. Now instead I get a bunch of warnings for every query (I assume):

[23.08.2009 11:28:39] - WARNING:You are using old syntax that will be removed in future versions

coupled with

A first chance exception of type 'System.ArgumentException' occurred in System.Core.dll

stemming from SubSonic.Core/Linq/Structure/QueryMapping.cs#L159 in Output tool window.

I ask what have I done wrong? Do I not deserve SubSonic???

MySqlConversionException:
{"Unable to convert MySQL date/time value to System.DateTime"} " at MySql.Data.Types.MySqlDateTime.GetDateTime()"

CREATE TABLE bugdatetime (
id INT NOT NULL AUTO_INCREMENT , PRIMARY KEY ( id )
);

INSERT INTO bugdatetime (id ) VALUES (NULL );

ALTER TABLE bugdatetime
ADD CreatedOn DATETIME NOT NULL;

The first row should have all 0's (CSV export for example):
"1";"0000-00-00 00:00:00"

Run Template tool, try bugdatetime.SingleOrDefault( x => x.id == 1 ); <- throws.

Implementation of
GetDateTime:

    /// <summary>Returns this value as a DateTime</summary>
    public DateTime GetDateTime()
    {
        if (!IsValidDateTime)
            throw new MySqlConversionException("Unable to convert MySQL date/time value to System.DateTime");

        return new DateTime(year, month, day, hour, minute, second);
    }

Implementation of
IsValidDateTime():

    /// <summary>
    /// Indicates if this object contains a value that can be represented as a DateTime
    /// </summary>
    public bool IsValidDateTime
    {
        get
        {
            return year != 0 && month != 0 && day != 0;
        }
    }

Now this is silly, who is MySQL to tell ME what I consider an invalid datetime value!

1. Delete the Products table from an Sqlite3 database.
   
2. Run the following code. This will add a Product to the Products table, creating the table in the process.
   

3. Result: The MyID field will have type nvarchar. Expected: MyID should have type nvarchar(10).

   namespace ss3simple {

   class Product
   {
       [SubSonicPrimaryKey]
       [SubSonicStringLength(10)]
       public string MyID { get; set; }
       [SubSonicStringLength(11)]
       public string Value { get; set; }
   
       public Product(string a, string b)
       {
           MyID = a;
           Value = b;
       }
   
       public Product() { }
   }
   
   class Program
   {
       static void Main(string[] args)
       {
           var repo = new SimpleRepository(SimpleRepositoryOptions.RunMigrations);
           repo.Add(new Product("hi", "there"));
       }
   }
   

   }

Using SQLite v3 and ActiveRecord pattern.Created a table named "User.Categories". When the T4 template is run, the "." in the name seems to cause a problem with namespaces and therefore class generation.

The line is:
if (col != null) ;
where it should be:
if (col != null)

The semi colon is erroneously added.

Anyone having trouble seeing open or all issue within github. Suppose I have several issues display within my dashboard. But when I tried to open, it always gives me empty result. I've tried with Firefox 3, chrome 2/3, and IE 7/8.

Anyone knows how to troubleshoot it ? Or it just github bug?

I need to be able to return a PagedList that is sorted descending by a column. The source code for the function seems to be assuming that ascending will be used. Why does it do that?

Using current build.

Line is:
if (col != null) ;

Should be:
if (col != null)

Hi using Subsonic 3.0.0.3 and MS SQL 2008 (10.0.2531) it appears there is some issue with Subsonic identifying Stored Procedure paramters as output parameters.

In the StoredProcedures.cs class I find my stored procedure definition but the last parameter is defined incorrectly as a 'AddParameter'.

sp.Command.AddParameter("HasPermission",HasPermission,DbType.Boolean);
When I sp.Execute() and attempt to read the value of the sp.Command.OutputValues[0] the value is null.

If the definition is edited to be like this;

sp.Command.AddOutputParameter("HasPermission", DbType.Boolean);
Then the value is returned and is correct value type

I am not sure how I 'fix' this - as everytime I regen the SP class via the 'Run Custom Tool' the parameter definitions require editing. Should I edit a T4 template somehow?

Please advise.

The GetTypeProjection method (line147) will throw an unhandled exception "Type 'System.Collections.Generic.IList`1[System.String]' does not have a default constructor" if i add some custom properties of type like IList{String},when the code runs at "return Expression.MemberInit(Expression.New(type), bindings);".

I think that the GetMappedMembers(type) method should exclude those custom fields and properties,because they don't belong to the ActiveRecord model,they are just custom added for certain biz intentions.

I attempted to make a primary key with UInt64, but no matter what I did it wouldn't run. however when I changed the datatype to long, it worked right on...

what are the datatypes allowed for the primary key?

I tried to find an attribute to disable autoinc key for the primary key, but couldn't find one.

is there a way to disable the auto increment so that I can set the id manually?

thanks!

SQL 2008 comes with the new datatype geography which is currently not supported. It should map to Microsoft.SqlServer.Types.SqlGeography or be converted to a byte array.

SimpleRepository.Update method throws NullReferenceException when called with non-null arguments.

Following model class:

public class Person
{
    public long ID {get;set;} 
    public string Name { get; set;}
}

ASP.NET MVC controller action:

[AcceptVerbs(HttpVerbs.Post)]
public ActionResult Edit(int id, FormCollection collection) {
    Person toUpdate = Repository.All<Person>().Single(p => p.ID == id); //is not null, found by ID. 
    TryUpdateModel(toUpdate, collection.ToValueProvider()); //updates model instance successfully. No property of model instance is null
    Repository.Update(toUpdate); //throws nullreferenceexception
    return RedirectToAction("Index");
}

stack trace:

at SubSonic.Query.Update.GetCommand()
at SubSonic.Query.Update.Execute()
at SubSonic.Repository.SimpleRepository.Update[T](T item)
at MvcApplication1.Controllers.PersonController.Edit(Int32 id, FormCollection collection)
in H:\...\Controllers\PersonController.cs:line 71"

No additional information is available in exception details.

My configuration: SubSonic 3, SQLite, empty database
Addtional notes: Querying and saving instances in db works with no problem

My code follow SubSonic's transaction documentation (reversed TransactionScope and SharedDbConnectionScope).

But I can not commit. Every time a second Save() method execute will throw an InvalidOperationException(already an open datareader).

My environment:
VS 2008, SQL 05/08 (chinese simplified)

StackTrace: System.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand command) +4848228
System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command) +23 System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async) +144 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +87 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12 System.Data.Common.DbCommand.ExecuteReader() +12 SubSonic.DataProviders.DbDataProvider.ExecuteReader(QueryCommand qry) +375 SubSonic.Repository.SubSonicRepository1.Add(T item, IDataProvider provider) +300 AGZ.MvcTest.Models.User.Add(IDataProvider provider) in D:\Writing - 代码撰写中\NewTech - 新技术测试\AGZ.MvcTest\AGZ.MvcTest\Models\_generated\ActiveRecord.cs:397 AGZ.MvcTest.Models.User.Save(IDataProvider provider) in D:\Writing - 代码撰写中\NewTech - 新技术测试\AGZ.MvcTest\AGZ.MvcTest\Models\_generated\ActiveRecord.cs:412 AGZ.MvcTest.Models.User.Save() in D:\Writing - 代码撰写中\NewTech - 新技术测试\AGZ.MvcTest\AGZ.MvcTest\Models\_generated\ActiveRecord.cs:406 AGZ.MvcTest.Controllers.ScopeTestController.Index() in D:\Writing - 代码撰写中\NewTech - 新技术测试\AGZ.MvcTest\AGZ.MvcTest\Controllers\ScopeTestController.cs:34 lambda_method(ExecutionScope , ControllerBase , Object[] ) +74 System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) +17 System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary2 parameters) +178 System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary2 parameters) +24 System.Web.Mvc.<>c__DisplayClassa.<InvokeActionMethodWithFilters>b__7() +52 System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func1 continuation) +254 System.Web.Mvc.<>cDisplayClassc.b9() +19 System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodWithFilters(ControllerContext controllerContext, IList1 filters, ActionDescriptor actionDescriptor, IDictionary2 parameters) +192 System.Web.Mvc.ControllerActionInvoker.InvokeAction(ControllerContext controllerContext, String actionName) +399 System.Web.Mvc.Controller.ExecuteCore() +126 System.Web.Mvc.ControllerBase.Execute(RequestContext requestContext) +27 System.Web.Mvc.ControllerBase.System.Web.Mvc.IController.Execute(RequestContext requestContext) +7 System.Web.Mvc.MvcHandler.ProcessRequest(HttpContextBase httpContext) +151 System.Web.Mvc.MvcHandler.ProcessRequest(HttpContext httpContext) +57 System.Web.Mvc.MvcHandler.System.Web.IHttpHandler.ProcessRequest(HttpContext httpContext) +7 System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +181 System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +75

There is bug in ExecuteScalar method in DbDataProvider class. It always gives weird result if the result from cmd.ExecuteScalar() is null. The result is not null nor in some kind of value. I'm using MySQL template and MySQL Connector 6.1.

So I modified the returned value like this

return result != null & !string.IsNullOrEmpty(result.ToString()) ? result : null;

Subsonic 3 will fail in several scenarios with security exceptions in medium trust.

Steps to reproduce

1. Create a 3.5 web application project
   
2. Add connection string for Northwind to web.config
   
3. Set
   
4. Add reference to subsonic.core
   
5. Add ActiveRecord templates to project (namespace modified to Northwind)
   

6. Add the following code to Page_Load of default.aspx

       var customers = Northwind.Customer.All();
   
       List<Northwind.Customer> list = new List<Northwind.Customer>();
   
       foreach (Northwind.Customer item in customers)
       {
           list.Add(item);
   
       }   
   

7. View page in browser

   Error produced is: System.Security.SecurityException: Request failed.

8. Replace code in Page_Load with

      var db = new Northwind.NorthwindDB();
   
       var customers = from c in db.Customers
                       select c;
   
       foreach (var item in customers)
       {
           Response.Write(item.CompanyName + "<br/>");
       }
   

9. Recompile and view page

   Error produced System.Security.SecurityException: Request failed.

Error occurs in DbQueryProvider
method Execute
statement Func fn = efn.Compile();

Seems the compile requires elevated permissions

Hello,

At the moment types like decimals are being parsed using the current culture by the LINQ parser. This should be done using invariant culture, otherwise you would have to switch to invariant culture before every linq query.

The SQLite database that I use has a table (call it TEST) with a DateTime field (call it DT). Until recently, I have been inserting records into the table by doing the following:
TEST t = new TEST(); TEST.DT = DateTime.Now; TEST.Save();

In SQLite (using SQLiteStudio), this will appear something like this: 2009-09-28 16:29:26.4145701.

Recently, I switched to using a different approach, so that I could control the database connection:
Insert.Into(x => x.DT).Values(DateTime.Now).Execute();

This appeared to work - no errors on insert. SQLite showed the value differently, as 9/28/2008 1:23 PM. Unfortunately, this difference becomes apparent when the data gets read; when that happens, an exception about converting strings to datetime is thrown.

Expected behavior?

I originally asked a question about this on StackOverflow here: http://stackoverflow.com/questions/1467820/subsonic-getpaged-method-different-index-needed-in-unit-test

Adam thought that this was a bug/inconsistency with the GetPaged method using ActiveRecord, and suggested that I log it here.

I have a SQLServer and a MySQL database having a few columns containing spaces.

The project is correctly generated using T4 templates, the spaces from column names are removed by CleanUp and the project compiles.

However at runtime those columns containg spaces in their names are not read from the db, by using e.g. myTable.GetAll() or any other method.

The debugger will show a SELECT statement where these columns are not present, meaning there is a bug while generating this statment by Subsonic.Core.

A brief intro to teh scenario:

I had created a DAL Library with subsonic 3.0 using default (activerecord.tt and such) .
This library is being consumed by WCF that has been generated using T4.
ActiveRecord has been modified to include WCF declarations such as [datamember], [ServiceContract], [OperationContract] and the _isLoaded has been exposed using a property called Loaded.
The WCF is then being consumed by an asp.net admin website that has been generated using T4.
The WCF will eventually be consumed by a silvelight app and a client application.

The problem:
Saving and deleting works but updates have issues with dirty columns.
The dirty columns are remembering the wrong columns that need to be updated regadless if it is a foreign key or just a regular column like a string or number column.

A column property looks like this:

string _Description;
[DataMember] public string Description
{

get { return _Description; }
set
{                               
    if(_Description!=value){
        if (_db == null) {
            _db = new DAL.Data.DALDB();
        }
        if (_repo == null && _db != null) _repo = new SubSonicRepository<Product>(_db);
        if (tbl == null && _repo != null) tbl=_repo.GetTable();
        _Description=value;
        var col=tbl.Columns.SingleOrDefault(x=>x.Name=="Description");
        if(col!=null){
            if (_dirtyColumns == null) _dirtyColumns = new List<IColumn>();
            if(!_dirtyColumns.Any(x=>x.Name==col.Name) && _isLoaded){
                _dirtyColumns.Add(col);
            }
        }
        OnChanged();
    }
}

}

When using the SimpleRepository 'Add' method and passing in an instance of 'T' where the primary key field is an integer (i.e public int PostID {...} in a class named 'Post') the return value is sent back typed as a decimal instead of an int. This appears to be related to how the data reader that reads back the 'SELECT SCOPE_IDENTITY' deals with that type.

I've seen this behavior on two different machines both of which were running against a local installation of SQL Server 2005 Express Edition.

in source code have a "#if debug console.writeline ..... " you can made something to enable write in a text file...

this will help a lot!!!

I deployd my website to a live server at a shared hosting porcider.

And get this error.
On my dev server it works fine and also the live DB works.
Please help me beacause is have been searching for a solutions for a week now and im stuk
Code:

protected void Page_Load(object sender, EventArgs e)
{
    var Countries = tblCountry.All();

    foreach (var country in Countries)
    {
        Response.Write(country.CountryName + "<br>");
    }


}

Stack Trace:

[SecurityException: Request failed.] System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed) +150 System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Object assemblyOrString, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed) +100 System.Security.CodeAccessSecurityEngine.CheckSetHelper(PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle rmh, Object assemblyOrString, SecurityAction action, Boolean throwException) +284 System.Security.PermissionSetTriple.CheckSetDemand(PermissionSet demandSet, PermissionSet& alteredDemandset, RuntimeMethodHandle rmh) +69 System.Security.PermissionListSet.CheckSetDemand(PermissionSet pset, RuntimeMethodHandle rmh) +150 System.Security.PermissionListSet.DemandFlagsOrGrantSet(Int32 flags, PermissionSet grantSet) +30 System.Threading.CompressedStack.DemandFlagsOrGrantSet(Int32 flags, PermissionSet grantSet) +40 System.Security.CodeAccessSecurityEngine.ReflectionTargetDemandHelper(Int32 permission, PermissionSet targetGrant, CompressedStack securityContext) +123 System.Security.CodeAccessSecurityEngine.ReflectionTargetDemandHelper(Int32 permission, PermissionSet targetGrant) +54

I am using SS 3.0.0.3 ActiveRecord (Current master build) with SQLite. This query:

new Update<BillDetail>(provider)
    .Set(bd => bd.DivisionDetails == divisionDetails)
    .Where(bd => bd.BillNumber == documentId && bd.IsInvoice == true);

produces this SQL:

UPDATE `BillDetails` 
    SET `DivisionDetails`=@up_DivisionDetails
    WHERE `BillNumber` = @0

Rob informed me here: http://stackoverflow.com/questions/1368405/subsonic-3-updatet-problem that the problem was fixed but it still is not.

I am using SS 3.0.0.3 ActiveRecord (Current master build) with SQLite (I think this error might be reproducable in other databases also, although I didn't checked).

If I do something like this:

var customerRepo = Customer.GetRepo();
var customerList = customerRepo.GetAll().ToList();

And then after making changes to the list, try to do this:

customerRepo.Update(customerList);

It causes a SQLite error because if the customer is not changed (i.e. IsDirty flag is false) the sql is not empty but this:

UPDATE `Customers`
    WHERE `Id` = @0;
UPDATE `Customers`
    WHERE `Id` = @1;

If a check for IsDirty flag is added to method public int Update(IEnumerable items, IDataProvider provider) at line 279 of SubSonic.Core\Repository\SubSonicRepository.cs, the error is resolved.

The resultant public int Update(IEnumerable items, IDataProvider provider) method should be:

    public int Update(IEnumerable<T> items, IDataProvider provider)
    {
        BatchQuery bQuery = new BatchQuery(provider);
        int result = 0;

        foreach(T item in items)
        {
            if (((IActiveRecord)item).IsDirty()) // adding this check fixes the problem...
            {
                var query = item.ToUpdateQuery(provider);
                bQuery.Queue(query);
            }
        }
        result = bQuery.Execute();
        return result;
    }

I am having a problem while using the test repository and it makes it IMPOSSIBLE to test...

This line of code:

     var dbFriend = Friend.SingleOrDefault(f => f.UserId == id && f.FriendId == friendId);

Always returns the first element regardless of whether the expression is matched or not. This happens because of

    var results=repo.Find(expression);

in the ActiveRecord.cs file.

I tried to step through it but couldn't figure it out.

My table looks like this:

FriendUserId - unique, bigint, auto increment
FriendId - big int
UserId - big in

I also noticed that the autoincrement features doesn't work in test mode. Is that correct behavior?

The update method in the test repository doesn't work. This method:

    public int Update(T item)
    {
        var update = _items.SingleOrDefault(x => x.KeyValue() == item.KeyValue());
        _items.Remove(item);
        _items.Add(update);
        return 0;
    }

always sets the variable "update" to null because x.KeyValue() == item.KeyValue() always fail because it is trying to compare two references instead of their values. Even though I have an item with the primary key = 3 it will still return null because it is boxing 3 and comparing two objects instead of two integers (I think!!!)

If I change the method to

    public int Update(T item)
    {
        var update = _items.SingleOrDefault(x => x.KeyValue().ToString() == item.KeyValue().ToString());
        _items.Remove(item);
        _items.Add(update);
        return 0;
    }

then the "update" variable is correctly retrieved but the PROBLEM now is

    _items.Remove(item)

does NOT remove the item from the collection... Not sure why???

I need some help. It is impossible to use the TestRepository in its current state...

I just started to use subsonic and I have an issue. I only checked it briefly.

When updating an active record object the schema name is missing. If you update the same object or delete it, the schema name is present in the SqlQuery which gets passed to the ANSISqlGenerator.cs. If the table is not under the dbo schema I, of course, get an exception.

In generated Structs.cs file, the schema name is present for the table.

ActiveRecord templates/3.0.0.3/VS08/Win7

There seems to be a bug in the ActiveRecord template that causes build problems when there are columns with the same name as the table they are contained in. The template properly suffixes these columns with "X", but then does not use this suffix in the DescriptorValue() and ToString() functions.

To repro:

1. Create any table with a field equal to the table name, for ex:
   CREATE TABLE Country(CountryID int, Country nvarchar(50))
   
2. Run ActiveRecord.tt
   
3. Build

UPDATE: Please nevermind this. I found the latest version of the templates at http://github.com/subsonic/SubSonic-3.0-Templates My appologies; I'm very new to Git.

Line 260 in SQLServer.ttinclude case for tiny int falls through to bit,
needs to be:

    case "tinyint":
        sysType=  "byte";
           break;

for example:
my Entity Class :
public class Org: IActiveRecord
{

public string fieldA
{get;set;}

public string fieldB
{get;set;}

}

and in my linq query:
var linqNodes = from o in DB.Orgs select new TreeNode{ id=o.fieldA,text=o.fieldB};
var nodes = linqNodes.ToList();

the TreeNode class is my custom class for tree node.
the nodes return record's field value is null.
I'm sure all records have the data in database.

In SqlQuery.cs line 610:

private void CreateJoin(string fromColumnName, string toColumnName, Join.JoinType type)

    {
        //see if we can find the table
        var toTable = _provider.FindOrCreateTable(typeof(T));

        //the assumption here is that the FromTable[0] is the table to join from
        if(FromTables.Count == 0)
            throw new InvalidOperationException("Can't join if there's no table to join to - make sure to use From() before InnerJoin");

        if(toTable == null)
            throw new InvalidOperationException("Can't find the table for this type. Try using the Column instead");

        var fromColumn = FromTables[0].GetColumn(fromColumnName);
        if(fromColumn == null)
            throw new InvalidOperationException("Don't know which column to join to - can't find column " + fromColumnName + " in table " + FromTables[0].Name);

        var toColumn = toTable.GetColumn(toColumnName);
        if(toColumn == null)
            throw new InvalidOperationException("Don't know which column to join to - can't find column " + toColumnName + " in table " + toTable.Name);

        CreateJoin(fromColumn, toColumn, Join.JoinType.Inner);
    }

It always use Join.JoinType.Inner...

As i know, there is no "left inner join" keyword in the ms sql server, so i think we should remove the "LeftInnerJoin" method or replace it with a new method "LeftJoin".

I am trying to bind the results of a Linq query to a gridview.

Here is the query:

    public IQueryable<PendingPaymentsTypedList> GetPendingPayments(DateTime dueDate)
    {
        var pp = from la in new Query<LoanApplication>()
                 join c in new Query<ContactInfo>() on la.UserId equals c.UserId
                 join e in new Query<EmailLog>() on la.LoanAppId equals e.LoanApplicationId into g
                 from e in g.DefaultIfEmpty()
                 select new PendingPaymentsTypedList()
                 {
                     LoanId = la.LoanAppId,
                     Customer = c.FirstName + " " + c.LastName,
                     LoanAmount = (decimal)la.LoanAmount,
                     Interest = (decimal)la.InterestAmount,
                     TotalDue = (decimal)(la.LoanAmount + la.InterestAmount),
                     EmailSent = (e != null)
                 };
        return pp;
    }

Here is the result when I try to bind to the gridview:

Exception of type 'System.Web.HttpUnhandledException' was thrown.
Error: Details :
System.Web.HttpUnhandledException: Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> System.Exception: The expression of type 'System.Linq.IQueryable1[<>f__AnonymousType12[<>f__AnonymousType02[ResponsibleLoan.Domain.Entities.LoanApplication,ResponsibleLoan.Domain.Entities.ContactInfo],System.Collections.Generic.IEnumerable1[ResponsibleLoan.Domain.Entities.EmailLog]]]' is not a sequence at

public List GetEmployeesByGroup(string group)

    {
        var value = group;

        return (from sge in Context.SecurityGroupEmployees
               join sg in Context.SecurityGroups on sge.SecurityGroupId equals sg.SecurityGroupId
               join lu in Context.Lookups on sg.SecurityGroupTypeId equals lu.LookupId
               where lu.Value == value
               select sge.Employee).ToList();

    }

for some reason TestMode Property makes it into protected override Expression VisitMemberAccess(MemberExpression m) in TSqlFormatter.

at this point it has writtent the following SQL

SELECT [t0].[Test], [t0].[EmployeeId] AS EmployeeId1, [t0].[EmployeeTypeId], [t0].[IsActive], [t0].[IsLockedOut], [t0].[Password], [t0].[PersonId], [t0].[UserName]
FROM [dbo].[SecurityGroupEmployee] AS t1
INNER JOIN [dbo].[SecurityGroup] AS t2
ON ([t1].[SecurityGroupId] = [t2].[SecurityGroupId]) INNER JOIN [dbo].[Lookup] AS t3
ON ([t2].[SecurityGroupTypeId] = [t3].[LookupId]) LEFT OUTER JOIN (
SELECT [t4].[EmployeeId], [t4].[EmployeeTypeId], [t4].[IsActive], [t4].[IsLockedOut], [t4].[Password], [t4].[PersonId], 1 AS Test, [t4].[UserName] FROM [dbo].[Employee] AS t4 ) AS t0 ON (([t0].[EmployeeId] = [t1].[EmployeeId]) AND (

the real issue is that the column TestMode does not exist and it is defined as MemberTypes.Field. so the unsupported exception is thrown. I am really not sure what is happening because LinqToSql does not have this much difficulty.

Hello guys,

I tried setting up the timeout without success. E.g.

        SubSonic.Query.QueryCommand q = new SubSonic.Query.QueryCommand(sql, db.Provider);            
        q.CommandTimeout = 120;
        q.Provider.ExecuteQuery(q);

But the command always timeouts after 60 seconds regardless of the value above.
So, shouldn't the method ToDbCommand in QueryCommand.cs be like this?

   public DbCommand ToDbCommand(DbTransaction trannie)
    {
        var connection = Provider.Factory.CreateConnection();
        connection.ConnectionString = Provider.ConnectionString;

        var cmd = connection.CreateCommand();
        cmd.CommandText = CommandSql;
        cmd.CommandType = CommandType.Text;
        cmd.CommandTimeout = this.commandTimeout; // is this missing?

        if(trannie != null)
            cmd.Transaction = trannie;

        foreach(var param in parameters)
        {
            DbParameter p = cmd.CreateParameter();
            p.ParameterName = param.ParameterName;
            p.Value = param.ParameterValue ?? DBNull.Value;
            p.DbType = param.DataType;
            cmd.Parameters.Add(p);
        }
        return cmd;
    } 

In protected override Expression VisitUnary(UnaryExpression u) of TSqlFormatter.cs. The excepcion only raises on a table with an great number of records.

This lines of code does not update the database.
No exception occured. It just dont update the data.


public ActionResult Edit(int id, Customer info)


    {
        try
        {
            info.ModifiedBy = 1; //UserID 
            info.Update();
            return RedirectToAction("Index");
        }
        catch
        {
            return View();
        }
    }

The Oracle provider does not remove newline characters, such as '\r\n'. "ORA-00936: missing expression" is the OracleException error when executing a select such as:

        Northwind db = new Northwind ("DevConn");
        var orders = from o in db.ORDERS
                     select o;
        foreach (var item in orders)
            Console.WriteLine(item.COMMODITY_CODE);

I'm using an Oracle Provider T4 template that I developed, and can provide a copy.

Hi I just used ActiveRecord.tt for creating ORM. When I dragged and dropped in my project the ActiveRecord.cs created successfully. But when I tried to build the project i got following compilation error :
"Error 4 Cannot implicitly convert type 'int?' to 'int'. An explicit conversion exists (are you missing a cast?) D:\projects\Testing\SubSnonicTest\SubSnonicTest\ActiveRecord\ActiveRecord.cs 11508 20 SubSnonicTest"

When I went in the code I found its coming from following method of ActiveRecord.cs:
public override int GetHashCode() {

        return this.int_UserID;
    }

I tried to figure out but could not. Please let me know its solution asap.

Thx in Advance
P. Ramesh

I have a problem in this line and i'm using the 3.0.0.3 version of subsonic.

_repo.Add(this,provider)

My record is saved but i got the error, "Object cannot be cast from DBNull to other types."
I think the problem it's because i have a foreign key that is also an index key in my db table and so it doesn't get a new id...

Can anyone help me with this?

Thanks.

If I use Find on the test repository, the objects that it returns don't have the _isNew property set to true. So, if you call Save() on the object, it creates a duplicate row in the test repository, rather than updating the existing one. This bug is in the git repo as of the time of this comment (I updated from the official release version to fix another problem with Find =) )

The Oracle-flavored SS3 doesn't handle converting CLOB/BLOB/NLOB/NCLOB to byte array (Byte[]).

The ODP.NET has a method for converting CLOB’s to string, similar to below:
OracleParameter paramHavingClobInIt;
string str = (paramHavingClobInIt.Value as OracleClob).Value; //C# syntax

The issue there is that the conversion is done in Subsonic.Core/Extensions/Database.cs and Objects.cs. Subsonic.Core is meant to be DB-agnostic, and we would have to make reference to the “Oracle.DataAccess” assembly to use the above.

Any novel ideas?

When calling Delete or DeleteMany in unit testing setup context before any other SimpleRepository method will lead to an error because Delete and DeleteMany do not run migrations.

I am trying to implement paging and sorting on a gridview using an objectsource with a select method that returns viewEnquiry.GetPaged(SortExpression, intPageIndex, maximumRows).

The paging is OK but when a gridview column is selected to sort in descending order the error "Incorrect syntax near the keyword 'DESC' occurs" (see full details below). The value passed in the SortExpression parameter is "DateCreated DESC"

Incorrect syntax near the keyword 'DESC'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'DESC'.

Source Error:

Line 7627:
Line 7628: Public Shared Function GetPaged(sortBy As String, pageIndex As Integer, pageSize As Integer) As PagedList(Of viewEnquiry)
Line 7629: Return GetRepo().GetPaged(sortBy, pageIndex, pageSize)
Line 7630: End Function
Line 7631:

Source File: C:\Documents and Settings\pholroyd.NA-SYS.000\My Documents\Visual Studio 2008\Projects\CRM\CRMData\ActiveRecord.vb Line: 7629

Stack Trace:

[SqlException (0x80131904): Incorrect syntax near the keyword 'DESC'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1950890 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4846875 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392 System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33 System.Data.SqlClient.SqlDataReader.get_MetaData() +83 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +297 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141 System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12 System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +10 SubSonic.DataProviders.DbDataProvider.ExecuteReader(QueryCommand qry) +377 SubSonic.DataProviders.DbDataProvider.ToList(QueryCommand qry) +81 SubSonic.Query.SqlQuery.ExecuteTypedList() +122 SubSonic.Repository.SubSonicRepository`1.GetPaged(String sortBy, Int32 pageIndex, Int32 pageSize) +518 CRM.CRM.Data.viewEnquiry.GetPaged(String sortBy, Int32 pageIndex, Int32 pageSize) in C:\Documents and Settings\pholroyd.NA-SYS.000\My Documents\Visual Studio 2008\Projects\CRM\CRMData\ActiveRecord.vb:7629 CRM.CRM.Data.ViewEnquiryDAL.GetEnquiries(Int32 maximumRows, Int32 startRowIndex, String SortExpression) in C:\Documents and Settings\pholroyd.NA-SYS.000\My Documents\Visual Studio 2008\Projects\CRM\DAL\CRMDAL.vb:41

[TargetInvocationException: Exception has been thrown by the target of an invocation.] System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +0 System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +71 System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) +261 System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) +29 System.Web.UI.WebControls.ObjectDataSourceView.InvokeMethod(ObjectDataSourceMethod method, Boolean disposeInstance, Object& instance) +488 System.Web.UI.WebControls.ObjectDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1247 System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +19 System.Web.UI.WebControls.DataBoundControl.PerformSelect() +142 System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +73 System.Web.UI.WebControls.GridView.DataBind() +4 System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82 System.Web.UI.WebControls.BaseDataBoundControl.OnPreRender(EventArgs e) +22 System.Web.UI.WebControls.GridView.OnPreRender(EventArgs e) +17 System.Web.UI.Control.PreRenderRecursiveInternal() +80 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +842

SortExpression = "DateCreated DESC".

Version Information: Microsoft .NET Framework Version:2.0.50727.3603; ASP.NET Version:2.0.50727.3082