using linq to select multiple columns with 1 unique one

using linq to select multiple columns with 1 unique one

Postby kerrywales » Thu 22 Apr 2010 00:30

I am selecting columns from two tables. A header table (with a unique reference) and a names table. The header table may have multiple names associated and so the new selected table can have rows of columns from both tables which has multiple rows containing the unique reference from the header table.

I would lime the resulting table to only have one row for each unique reference. I am not bothered which of the lines from the joined data is used so long as it is one. e.g. the current linq query is:
var query = from Header in myTables.TblHead
from Name in Header.TblNames
where Header.Name.ToUpper().Contains(surname.ToUpper())
|| Name.Company.ToUpper().Contains(surname.ToUpper())
|| Name.Surname.ToUpper().Contains(surname.ToUpper())
orderby Header.Reference, Header.Name
select new
{
Header.Reference,
Header.Name,
Name.Surname,
Name.Company
};

So if the constraints has 4 rows in TblNames linked to TblHead with reference "ABC" I will have 4 rows in the query result. I would like to have one row in the result since the first two columns are the most important and the last two columns are useful but not essential hence it doesn't matter which one is shown.

I have googled so many sites trying all sorts of recommendations, but none of them succeed. Would be greatful is someone can help.

I am using c# in VS 2008 with devart to linq to my Postgresql db.

Regards
kerrywales
 
Posts: 52
Joined: Tue 05 Jan 2010 12:26

Postby AndreyR » Fri 23 Apr 2010 11:31

There is no easy way to create a LINQ query implementing the scenario you are describing.
Try to write plain SQL solving the problem and then execute this SQL using the DataContext.Query method.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby kerrywales » Tue 27 Apr 2010 12:42

Thanks for your response but I have worked out a solution by flipping the search on its head.

var query = from Name in entity.TblName
where Name.TblHead.Name.ToUpper().Contains(surname.ToUpper())
|| Name.TblName.Company.ToUpper().Contains(surname.ToUpper())
|| Name.TblName.Surname.ToUpper().Contains(surname.ToUpper())
group Name by Name.TblHead.Reference into g
select new
{
g.FirstOrDefault().TblHead.Reference,
Name = g.FirstOrDefault().TblHead.Name.TrimEnd(),
Surname = g.FirstOrDefault().TblName.Surname.TrimEnd(),
Company = g.FirstOrDefault().TblName.Company.TrimEnd(),
MatchesInHead = g.Count()
}

This gives me the one Head Reference that I want and also tells me how many names (including the one on display) are linked to the Reference I want. TblHead and TblName are linked in the constraints definition within the database.

Kerry
:lol:
kerrywales
 
Posts: 52
Joined: Tue 05 Jan 2010 12:26

Postby AndreyR » Tue 27 Apr 2010 13:17

Thank you for sharing your knowledge.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for PostgreSQL