Get distinct month and year with SubSonic

Question

I have date type column (MySQL):

SELECT invdate FROM invoices;

invdate
-------
2009-08-22
2009-07-12
2009-08-23
-------

and I want get month and year like this:

SELECT DISTINCT MONTH(invdate) AS invmonth, YEAR(invdate) AS invyear FROM invoices;

how to use in C# with SubSonic (SimpleRepository)?

TIA

Answer 1

Have you had a chance to have a look at the docs? See the link and code below, it should help.

http://subsonicproject.com/docs/Distinct

[Test]
public void SqlQuery_when_setting_distinct_it_should_set_IsDistinct()
{
    SubSonic.SqlQuery query= new 
     Select(Product.SupplierIDColumn).From<Product>().Distinct();
    Assert.IsTrue(query.IsDistinct);
}

[Test]
public void SqlQuery_should_handle_distinct()
{
    ProductCollection select = new 
     Select(Product.SupplierIDColumn).From<Product>().Distinct()
     .ExecuteAsCollection<ProductCollection>();

    Assert.AreEqual(29, select.Count);

}

[Test]
public void SqlQuery_GetRecordCount_should_handle_distinct()
{
    int select = new Select(Product.SupplierIDColumn)
     .From<Product>().Distinct()
     .GetRecordCount();

    Assert.AreEqual(29, select);
}

Answer 2

I have read SubSonic documents, and about DISTINCT no problem anymore.

But the problem is how to get Month/Year from Date/Datetime field?

I once read an article about how to get the value of Month/Year in LinqToSql, the code (maybe) like this:

var periode = from p in invoices
        select new
        {
       month = p.invdate.Create.Month,
       year = p.invdate.Create.Year
        };

But I dont know how to using above code in SubSonic, because SubSonic cannot give 'Create' function like above code.

I need a clear answer from you all.

TIA

Answer 3

OMG! I forgot to define variable as date/datetime, so I can not use these functions.

before:

public string invdate { get; set; }

after:

public DateTime invdate { get; set; }

My mistake, I'm sorry. Problem solved.