table names split on test...

Apr 8, 2009 at 9:02 PM
Hi there,
awesome toolkit!  I'm running into a weird issue maybe someone can shed some light on.
I created two classes, initially.

<pre>
    public class ComplianceRule
    {
        public int ID;
        public int ExceptionQueryMasterID;
        public int GroupID;
        public int Active;
        public int Visible;
        public DateTime CreatedDate;
        public string CreatedBy;
        public DateTime ModifiedDate;
        public string ModifiedBy;
        public List<ComplianceRuleInput> ComplianceRuleInput;
    }

    public class ComplianceRuleInput
    {
        public int ID;
        public ComplianceRule ComplianceRule;
        public int Position;
        public string Value;
        public DateTime CreatedDate;
        public string CreatedBy;
        public DateTime ModifiedDate;
        public string ModifiedBy;
    }
</pre>

and I am running a simple test, which I would think should work.
TestQuery(db.ComplianceRule.Where(c => db.ComplianceRuleInput.Where(o => o.ComplianceRule.ID == c.ID).All(o => o.CreatedDate.Year == 2008))
                );  

Here is what I get when I run the test:  My table is defined as ComplianceRule but for just this particular test it is coming out as [Compliance Rules].  I've found the methods that split the word trying to determine an alias but WHY is it happening?
<pre>

Running tests: TranslationTests

Queryable.Where(
  Query(Test.ComplianceRule),
  c => Queryable.All(
    Queryable.Where(
      Test.TranslationTests.db.ComplianceRuleInput,
      o => o.ComplianceRule.ID == c.ID
    ),
    o => o.CreatedDate.Year == 2008
  )
)
==>
SELECT t0.Active, t0.CreatedBy, t0.CreatedDate, t0.ExceptionQueryMasterID, t0.Gr
oupID, t0.ID, t0.ModifiedBy, t0.ModifiedDate, t0.Visible
FROM [ComplianceRule] AS t0
WHERE NOT EXISTS(
  SELECT NULL
  FROM [ComplianceRuleInput] AS t1
  LEFT OUTER JOIN [Compliance Rules] AS t2
    ON (((((t2.CreatedBy = t1.CreatedBy) AND (t2.CreatedDate = t1.CreatedDate))
AND (t2.ID = t1.ID)) AND (t2.ModifiedBy = t1.ModifiedBy)) AND (t2.ModifiedDate =
 t1.ModifiedDate))
  WHERE ((t2.ID = t0.ID) AND NOT (YEAR(t1.CreatedDate) = 2008))
  )
Query failed to execute:
SELECT t0.Active, t0.CreatedBy, t0.CreatedDate, t0.ExceptionQueryMasterID, t0.Gr
oupID, t0.ID, t0.ModifiedBy, t0.ModifiedDate, t0.Visible
FROM [ComplianceRule] AS t0
WHERE NOT EXISTS(
  SELECT NULL
  FROM [ComplianceRuleInput] AS t1
  LEFT OUTER JOIN [Compliance Rules] AS t2
    ON (((((t2.CreatedBy = t1.CreatedBy) AND (t2.CreatedDate = t1.CreatedDate))
AND (t2.ID = t1.ID)) AND (t2.ModifiedBy = t1.ModifiedBy)) AND (t2.ModifiedDate =
 t1.ModifiedDate))
  WHERE ((t2.ID = t0.ID) AND NOT (YEAR(t1.CreatedDate) = 2008))
  )
Test 1: TestAllWithSubquery - FAILED
Reason: Invalid object name 'Compliance Rules'.

SUMMARY: TranslationTests
Total tests run: 1
Total tests passed: 0
Total tests failed: 1

 </pre>

Apr 8, 2009 at 9:22 PM

I noticed that issue too.  If you are using QueryableTable<> or UpdateableTable<>, make sure you are passing the physical name of the table of the constructor:

new UpdateableTable<ComplianceRule>(myProvider, “ComplianceRule”);

I’m not sure whether it’s a bug or a feature, but the code seems to split the table names where it finds a capital letter.

From: CT_KLEIN [mailto:notifications@codeplex.com]
Sent: Wednesday, April 08, 2009 2:03 PM
To: Erik Johnson
Subject: table names split on test... [IQToolkit:52733]

From: CT_KLEIN

Hi there,
awesome toolkit! I'm running into a weird issue maybe someone can shed some light on.
I created two classes, initially.


public class ComplianceRule
{
public int ID;
public int ExceptionQueryMasterID;
public int GroupID;
public int Active;
public int Visible;
public DateTime CreatedDate;
public string CreatedBy;
public DateTime ModifiedDate;
public string ModifiedBy;
public List<ComplianceRuleInput> ComplianceRuleInput;
}

public class ComplianceRuleInput
{
public int ID;
public ComplianceRule ComplianceRule;
public int Position;
public string Value;
public DateTime CreatedDate;
public string CreatedBy;
public DateTime ModifiedDate;
public string ModifiedBy;
}



and I am running a simple test, which I would think should work.
TestQuery(db.ComplianceRule.Where(c => db.ComplianceRuleInput.Where(o => o.ComplianceRule.ID == c.ID).All(o => o.CreatedDate.Year == 2008))
);

Here is what I get when I run the test: My table is defined as ComplianceRule but for just this particular test it is coming out as [Compliance Rules]. I've found the methods that split the word trying to determine an alias but WHY is it happening?

Running tests: TranslationTests

Queryable.Where(
Query(Test.ComplianceRule),
c => Queryable.All(
Queryable.Where(
Test.TranslationTests.db.ComplianceRuleInput,
o => o.ComplianceRule.ID == c.ID
),
o => o.CreatedDate.Year == 2008
)
)
==>
SELECT t0.Active, t0.CreatedBy, t0.CreatedDate, t0.ExceptionQueryMasterID, t0.Gr
oupID, t0.ID, t0.ModifiedBy, t0.ModifiedDate, t0.Visible
FROM [ComplianceRule] AS t0
WHERE NOT EXISTS(
SELECT NULL
FROM [ComplianceRuleInput] AS t1
LEFT OUTER JOIN [Compliance Rules] AS t2
ON (((((t2.CreatedBy = t1.CreatedBy) AND (t2.CreatedDate = t1.CreatedDate))
AND (t2.ID = t1.ID)) AND (t2.ModifiedBy = t1.ModifiedBy)) AND (t2.ModifiedDate =
t1.ModifiedDate))
WHERE ((t2.ID = t0.ID) AND NOT (YEAR(t1.CreatedDate) = 2008))
)
Query failed to execute:
SELECT t0.Active, t0.CreatedBy, t0.CreatedDate, t0.ExceptionQueryMasterID, t0.Gr
oupID, t0.ID, t0.ModifiedBy, t0.ModifiedDate, t0.Visible
FROM [ComplianceRule] AS t0
WHERE NOT EXISTS(
SELECT NULL
FROM [ComplianceRuleInput] AS t1
LEFT OUTER JOIN [Compliance Rules] AS t2
ON (((((t2.CreatedBy = t1.CreatedBy) AND (t2.CreatedDate = t1.CreatedDate))
AND (t2.ID = t1.ID)) AND (t2.ModifiedBy = t1.ModifiedBy)) AND (t2.ModifiedDate =
t1.ModifiedDate))
WHERE ((t2.ID = t0.ID) AND NOT (YEAR(t1.CreatedDate) = 2008))
)
Test 1: TestAllWithSubquery - FAILED
Reason: Invalid object name 'Compliance Rules'.

SUMMARY: TranslationTests
Total tests run: 1
Total tests passed: 0
Total tests failed: 1

Read the full discussion online.

To add a post to this discussion, reply to this email (IQToolkit@discussions.codeplex.com)

To start a new discussion for this project, email IQToolkit@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe or change your settings on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com


This e-mail is for the use of the intended recipient(s) only. If you have received this e-mail in error, please notify the sender immediately and then delete it. If you are not the intended recipient, you must not use, disclose or distribute this e-mail without the author's prior permission. We have taken precautions to minimize the risk of transmitting software viruses, but we advise you to carry out your own virus checks on any attachment to this message. We cannot accept liability for any loss or damage caused by software viruses. Any views and/or opinions expressed in this e-mail are of the author only and do not represent the views of Epicor Software Corporation or any other company within its group.
Apr 9, 2009 at 1:35 PM
yeah, thats exactly how I have it setup
new UpdateableTable<ComplianceRule>(myProvider, “ComplianceRule”);
and I've tried it with both UpdateableTable and QueryableTable.  It must be a 'feature' because I've noticed that the feature exists in LINQPad also to 'Pluralize EntitySet and Table properties, but there at least it's an optional feature.
Still, this is a pretty interesting project and hopefully it continues to grow.
Apr 9, 2009 at 6:59 PM

Are you compiling the query using QueryCompiler.Compile()?  I can duplicate the problem by compiling the query, but only in the “isolated” mode (see NorthwindExecutionTest.cs for examples) where the class holding the record type is provided as an argument.  It fails executing for test6 below – the others work fine.

TestDb db = new TestDb(new SqlQueryProvider(con, TestDb.StandardPolicy, null));

var test4 = (from c in db.PurAgents

            where c.Company == "01"

            select c).ToList();

var fn4 = QueryCompiler.Compile((string CompanyID) =>

       db.PurAgents.Where(c => c.Company == CompanyID));

var test5 = fn4("01").ToList();

    

var iso  = QueryCompiler.Compile((TestDb db2, string id) => db2.PurAgents.Where(c => c.Company == id));

var test6 = iso(db, "01"); // BLOWS UP HERE by pluralizing the table name!!!

From: CT_KLEIN [mailto:notifications@codeplex.com]
Sent: Thursday, April 09, 2009 6:35 AM
To: Erik Johnson
Subject: Re: table names split on test... [IQToolkit:52733]

From: CT_KLEIN

yeah, thats exactly how I have it setup
new UpdateableTable<ComplianceRule>(myProvider, “ComplianceRule”);
and I've tried it with both UpdateableTable and QueryableTable. It must be a 'feature' because I've noticed that the feature exists in LINQPad also to 'Pluralize EntitySet and Table properties, but there at least it's an optional feature.
Still, this is a pretty interesting project and hopefully it continues to grow.

Read the full discussion online.

To add a post to this discussion, reply to this email (IQToolkit@discussions.codeplex.com)

To start a new discussion for this project, email IQToolkit@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe or change your settings on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com


This e-mail is for the use of the intended recipient(s) only. If you have received this e-mail in error, please notify the sender immediately and then delete it. If you are not the intended recipient, you must not use, disclose or distribute this e-mail without the author's prior permission. We have taken precautions to minimize the risk of transmitting software viruses, but we advise you to carry out your own virus checks on any attachment to this message. We cannot accept liability for any loss or damage caused by software viruses. Any views and/or opinions expressed in this e-mail are of the author only and do not represent the views of Epicor Software Corporation or any other company within its group.
Apr 9, 2009 at 8:22 PM

My solution was to implement a custom ImplicitMapping object and use it when I initialize the provider.  This is the simple case where the type name matches the table name.

class EpiMapping : ImplicitMapping

{

    public EpiMapping(QueryLanguage language)

        : base(language)

    {

    }

    public override string GetTableName(MappingEntity entity)

    {

        return entity.Type.Name;

    }

}

****

public class TestDb

{

    public IUpdatableTable<PurAgent> PurAgents;

    private IQueryProvider provider;

    public static QueryPolicy StandardPolicy = new QueryPolicy(new EpiMapping(new TSqlLanguage()));

    public TestDb(IQueryProvider provider)

    {

        this.provider = provider;

        this.PurAgents = new UpdatableTable<PurAgent>(this.provider);

    }

}

****

using (SqlConnection con = new SqlConnection(connectionString))

{

   con.Open();

   TestDb db = new TestDb(new SqlQueryProvider(con, TestDb.StandardPolicy, null));

   var iso  = QueryCompiler.Compile((TestDb db2, string id) => db2.PurAgents.Where(c => c.Company == id));

   var test6 = iso(db, "01").ToList();

}

From: CT_KLEIN [mailto:notifications@codeplex.com]
Sent: Thursday, April 09, 2009 6:35 AM
To: Erik Johnson
Subject: Re: table names split on test... [IQToolkit:52733]

From: CT_KLEIN

yeah, thats exactly how I have it setup
new UpdateableTable<ComplianceRule>(myProvider, “ComplianceRule”);
and I've tried it with both UpdateableTable and QueryableTable. It must be a 'feature' because I've noticed that the feature exists in LINQPad also to 'Pluralize EntitySet and Table properties, but there at least it's an optional feature.
Still, this is a pretty interesting project and hopefully it continues to grow.

Read the full discussion online.

To add a post to this discussion, reply to this email (IQToolkit@discussions.codeplex.com)

To start a new discussion for this project, email IQToolkit@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe or change your settings on codePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com


This e-mail is for the use of the intended recipient(s) only. If you have received this e-mail in error, please notify the sender immediately and then delete it. If you are not the intended recipient, you must not use, disclose or distribute this e-mail without the author's prior permission. We have taken precautions to minimize the risk of transmitting software viruses, but we advise you to carry out your own virus checks on any attachment to this message. We cannot accept liability for any loss or damage caused by software viruses. Any views and/or opinions expressed in this e-mail are of the author only and do not represent the views of Epicor Software Corporation or any other company within its group.
Coordinator
May 7, 2009 at 12:49 AM
This is a behavior I put in the implicit mapping object.  The primary goal of this mapping object was to get Northwind database to somewhat work.  You can either switch to using the new attribute or xml based mapping or devise your own variant.