Fluent NHibernate map sql Count() column

Question

I got a small problem here where I need to map a count() column in NHibernate. I'm really bad at NHibernate, but this is the task I got that I need to solve.

Sorry for the Swedish in the code I will post here, it's an old database that originally only was in Swedish. But really, you only need to look at the first row. But I post the whole thing just in case.

SELECT COUNT(Matpunkt.id_matpunkt) AS [Count], 
            f.ID_Företag,
            f.Namn,
            f.Adress,
            f.Postnummer,
            f.Postadress,
            f.Tele,
            f.Orgnummer,
            f.Typ,
            f.Logo,
            f.Fax,
            f.Mallkatalog,
            f.Tidszon,
            u.Ansvarigtkontor AS [AnsvKontor]

            FROM Matpunkt 
            INNER JOIN MatpunktInstrumentParameter ON Matpunkt.id_matpunkt = MatpunktInstrumentParameter.id_matpunkt 
            INNER JOIN Uppdrag u ON Matpunkt.id_uppdrag = u.ID_Uppdrag 
            INNER JOIN Företag f ON u.Ansvarigtkontor = f.ID_Företag
            WHERE MatpunktInstrumentParameter.tid_ner IS NULL
            GROUP BY f.ID_Företag, f.Namn, f.Adress, f.Postnummer, f.Postadress, f.Tele, f.Orgnummer, f.Typ, f.Logo, f.Fax, f.Mallkatalog, f.Tidszon, u.Ansvarigtkontor
            ORDER BY [Count] desc

Also I don't know if I should use AS [Count] or AS 'Count'. Or even SELECT COUNT(*) instead of what I use, OR if I even need to use an alias all together... Yeah I don't know what is best practice in sql. To be honest, I didn't write this query. It was originally in an webforms asp:SqlDataSource element. But now the whole site but this one is "upgraded" to use MVC and AngularJS. I only ask you here since my colleagues are on holiday.

And here is the mapping class:

public CompanyMap()
    {
        Table("[Företag]");
        Id(x => x.ID, "ID_Företag").Access.PascalCaseField(Prefix.Underscore);
        Map(x => x.Name, "Namn").Not.Nullable();
        Map(x => x.Address, "Adress");
        Map(x => x.ZipCode, "Postnummer");
        Map(x => x.PostAddress, "Postadress");
        Map(x => x.PhoneNumber, "Tele");
        Map(x => x.CorporateIdentityNumber, "Orgnummer");
        Map(x => x.Type, "Typ");
        Map(x => x.Logo, "Logo");
        Map(x => x.Fax, "Fax");
        References(x => x.Office, "AnsvKontor").Nullable();

        Map(x => x.TemplateFolder, "Mallkatalog");

        Map(x => x.TimeZone, "Tidszon").Nullable();

        Map(x => x.Count, "Count");

        HasMany<User>(x => x.Users).KeyColumn("ID_Företag");
    }

And here nicely mixed Swedish and English, but you only need to look at the row that says Map(x => x.Count, "Count");. Edit: The reason for lines like the last one is that this class is used for several different things on the site if you're wondering why they are there and not used by me.

FYI this mapping works without that row. But then I just get null back on Count.

[
  {
    "id": 1,
    "name": "dummytext - Stockholm",
    "address": "dummytext 34",
    "zipCode": "123 45",
    "postalAddress": "Stockholm",
    "phone": "123456789",
    "corporateIdentityNumber": "123456-789",
    "count": null
  },

Yeah I don't want to give you the real data... I think you'll understand. And since I'm bad at NHibernate and I'm just adding stuff to a very big solution I don't know if I need to add this row here:

public static void Create()
    {
        Mapper.CreateMap<Company, CompanyViewModel>()
            .ForMember(vm => vm.PostalAddress, cfg => cfg.MapFrom(c => c.PostAddress))
            .ForMember(vm => vm.Phone, cfg => cfg.MapFrom(c => c.PhoneNumber))
            .ForMember(vm => vm.Count, cfg => cfg.MapFrom(c => c.Count));
    }
}

Finally the exception:

Invalid column name 'Count'.

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: Invalid column name 'Count'.

And stacktrace:

Hopefully you can open this image to a bigger size.

And as a last thing I want to say, if you ask me complex things about NHibernate I may not be able to answer you, like "Why aren't you doing this instead, bla bla?". I can't answer that obviously for reasons stated above.

Thank you for your time in advance for reading this long post!

Does Count column exists in your table that are you trying to map using fluent nhibernate? — jtabuloc
– jtabuloc, Commented Jul 21, 2015 at 9:25
@JTC No it doesn't. This query tells us how many measurementpoints there are for each office in the company. So it creates one with the name Count. — Philip B
– Philip B, Commented Jul 21, 2015 at 9:28
I think you shouldn't try to map this, but just write an NHibernate query ... — Stefan Steinegger
– Stefan Steinegger, Commented Jul 21, 2015 at 9:39
@PhilipB, If Count column doesn't exists your Map(x => x.Count, "Count"); won't work and it will throw an exception as mentioned in the picture you attached. In this case you need to use Nhibernate query like QueryOver as mentioned by @Stefan — jtabuloc
– jtabuloc, Commented Jul 21, 2015 at 10:00
Ok, could any of you please write an example since I have no clue how to do it? — Philip B
– Philip B, Commented Jul 21, 2015 at 10:18

Stefan Steinegger · Accepted Answer · 2015-07-21 12:10:11Z

1

Create a query with HQL or QueryOver. The query is based on your classes, not tables, and there is some information missing. I just used my fantasy to fill the gaps. There is a plain rewrite of your query in HQL, assuming some properties in your class model:

    SELECT count(Matpunkt.id) AS `Count`, 
        f.ID_Företag,
        f.Namn,
        f.Adress,
        f.Postnummer,
        f.Postadress,
        f.Tele,
        f.Orgnummer,
        f.Typ,
        f.Logo,
        f.Fax,
        f.Mallkatalog,
        f.Tidszon,
        u.Ansvarigtkontor AS `AnsvKontor`
    FROM Matpunkt m
        join m.InstrumentParameter ip
        join m.Uppdrag u
        join u.Företag f
    WHERE ip.Ner IS NULL
    GROUP BY 
        f.ID_Företag, 
        f.Namn, 
        f.Adress, 
        f.Postnummer, 
        f.Postadress, 
        f.Tele, 
        f.Orgnummer, 
        f.Typ, 
        f.Logo, 
        f.Fax, 
        f.Mallkatalog, 
        f.Tidszon, 
        u.Ansvarigtkontor
    ORDER BY `Count` desc

You get an object[] back, which is not very nice when working with an ORM.

Frankly I guess it can be simplified a lot. If I had your class model, I probably could come up with a much better solution. What actually do you want to count, from point of view of a Företag, which seems to be the central part here?

You should also consider the pure object oriented approach, where you get fully initialized objects to the memory and do some processing there (only do this with a few records).

answered Jul 21, 2015 at 12:10

Stefan Steinegger

64.8k17 gold badges132 silver badges193 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Philip B Over a year ago

The object Company(Företag in swedish if you wondered :P) has the properties you'd expect from such a class(Name, Address, PostAddress etc). CompanyViewModel has the properties you see in the JSON object in my question. This query is counting how many measurementpoints(table Matpunkt) an office has. So what I get in SQL Management Studio is all those columns where Count is one of them. So one of our offices has 326 measurementpoints and another one only has 87. But do you think I need to change the sql? That one has been used in that aspx element for a few years. Sorry for the confusion.

Stefan Steinegger Over a year ago

Of course you could also keep using SQL... Usually HQL is simpler, depending on how well the class model fits to the problem, but sometimes it drives you crazy.

Philip B Over a year ago

I've never used HQL before. Never even heard of it before today^^ what you wrote was in HQL? And that returns an object? and in that object I will have my count? :P

Collectives™ on Stack Overflow

Fluent NHibernate map sql Count() column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related