Hello,

How can I produce the following sql:

select Min(id) as id, name from dbo.names group by name order by id

The only difference should be that the Linq should return an Entity rather than an anonymous type.

Thank you

I have it working using the following:

            var query = from b in metaData.Names
                        group b by b.Name into a
                        select new { Entity = a, Id = a.Min(x => x.Id) } into result
                        orderby result.Id
                        select result;


            return query.ToList().Select(x=>x.Entity.First()).ToList();

However, this will retrieve a lot of unwanted data which I then discard by saying:

.Select(x=>x.Entity.First())

Is there any way to reduce the subset on the database side as my above SQL does?

Cheers

Are you working with Jed (South Africa), coz he just asked the same question here: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=19147 ? Anyway...I'm checking it out...hold on.

(Edit) Here you are:

                var q = from c in metaData.OrderDetail
                        orderby c.Quantity
                        group c by c.ProductId into g
                        select new OrderDetailEntity() { ProductId = g.Key, Quantity = g.Min(c => c.Quantity) } into results
                        orderby results.Quantity
                        select results;

Ah I didn't see his post, sorry.

Yes my linq is very similar to that which you posted. The problem is that Linq Min returns an int whereas SQL Min returns a record.

When I use .First() within the LLBL Gen part of the query, LLBL Gen throws an exception. This forces me to use .First() only after the data has been retrieved from the database.

Just reiterating for clarity

Cheers

SQL Min returns a record.

AFAIK: in SQL, MIN is an aggregate function that works on a column (set of values) to return the minimum value. So it should return a value not a row.

Am I missing something?

When used in conjunction with a group by it only returns 1 record per group.

Kizedek wrote:

When used in conjunction with a group by it only returns 1 record per group.

No, MIN(<name>) returns the lowest value in the set of values in the column with name <name>. If you use it in a filter, you get the rows which have that value, but it doesn't return a row

Thanks for the reply.

I understand what you are saying, but my original question still stands. How can I achieve my original post's SQL example using LLBL Gen? The Linq example that Walaa posted is close, but it returns unnecessary data which will prove to be a big problem in the future.

Am I missing something?

Could you please examine the SQL generated from the linq code I've posted and tell us how would you like to modify it. And/Or specify the unnecessary data that you need to eliminate.

Well it churns out quite a few SQL statements.

The last statement is:

SELECT FIELD1, NAME, ID, FIELD2, FIELD3 FROM TABLENAME [LPA_L1]
WHERE ( ( ( ( [LPA_L1].[NAME] IN ('VALUE1', 'VALUE2')))))

Which returns all of the records.

I am quite pressed for time so I think I will just write a small class to return just the Id and the Name rather than the whole entity.

On a side note, is every SQL statement in the output actually executed? I noticed that even the simple anonymous type query outputs 3 separate SQL queries. The first one is perfect, the other two unnecessarily wrap the first SQL within sub queries.

All of the sql should be executed - I doubt if the other two are actually redundant. You can check what is actually being executed by using SQL profiler to see what actually hits the server.

Matt

If you enable DQE tracing, and the query contains subqueries, the subqueries are shown as separate queries in the trace. To have the queries which are executed, enable ORMPersistenceExecution trace flag to level 4.

Thanks for your help!