LINQ to Entities query in “dot notation?

Up vote 2 down vote favorite 1 share g+ share fb share tw.

I have two entities in my ASP. Net MVC3 application. I am using EF 4.1 Table("tblAccount") public class Account { Key Column("Creditor Registry ID", Order = 0) public int CreditRegistryId { get; set; } Key Required Column("Account No", Order = 1) public int AccountNo { get; set; } Column("Minimum Installment") public decimal MinimumInstallment { get; set; } Column("Account Status Date") public DateTime AccountStatusDate { get; set; } Required Column("Account Type") public string AccountType { get; set; } public virtual ICollection AccountOwners { get; set; } } and Table("tblAccountOwner") public class AccountOwner { Key ForeignKey("Account") Column("Creditor Registry ID", Order = 0) public int CreditorRegistryId { get; set; } Key ForeignKey("Account") Column("Account No", Order = 1) public int AccountNo { get; set; } Key Column("Account Owner Registry ID", Order = 2) public long AccountOwnerRegistryId { get; set; } public virtual Account Account { get; set; } } I need to convert following query to a LINQ to Entities query using extension method "dot" notation: SELECT Sum(ABS(Minimum Installment)) AS SumOfMonthlyPayments FROM tblAccount INNER JOIN tblAccountOwner ON tblAccount.

Creditor Registry ID = tblAccountOwner. Creditor Registry ID AND tblAccount. Account No = tblAccountOwner.

Account No WHERE (tblAccountOwner. Account Owner Registry ID = 731752693037116688) AND (tblAccount. Account Type NOT IN ('CA00', 'CA01', 'CA03', 'CA04', 'CA02', 'PA00', 'PA01', 'PA02', 'PA03', 'PA04')) AND (DATEDIFF(mm, tblAccount.

State Change Date, GETDATE()) 999) I tried following query: var minIns = context. Accounts . Where(x=>x.

CreditRegistryId == x.AccountOwners. Any(z=>z. AccountOwnerRegistryId) .

Sum(p => Math. Abs(p. MinimumInstallment)); but it is not working.

How can I write it? C# linq linq-to-entities entity-framework-4.1 link|improve this question edited Apr 3 '11 at 11:09svick28.4k62060 asked Apr 3 '11 at 8:41DotnetSparrow1,3341833 61% accept rate.

Var ownerRegId = 731752693037116688L; var excludeTypes = new { "CA00", "CA01", "CA03", "CA04", "CA02", "PA00", "PA01", "PA02", "PA03", "PA04" }; var maxStateChangeMonth = 4; var excludeStatusId = 999; var SumOfMonthlyPayments = context. AccountOwners . Where(ao => ao.

AccountOwnerRegistryId == ownerRegId &&!excludeTypes. Contains(ao.Account. AccountType) && (ao.Account.

StateChangeDate == null || ao.Account.StateChangeDate. Month - DateTime.Now. Month Math.

Abs(ao.Account. MinimumInstallment)); or var SumOfMonthlyPayments = (from ao in context. AccountOwners let a = ao.

Account where ao. AccountOwnerRegistryId == ownerRegId &&!excludeTypes. Contains(a.

AccountType) && (a. StateChangeDate == null || a.StateChangeDate. Month - DateTime.Now.

Month Math. Abs(ao.Account. MinimumInstallment)).

Query notation is just as good as the "dot notation. " Anyhow, here's my shot at it. I believe everything used here should be translatable.

Var ownerRegId = 731752693037116688L; var excludeTypes = new { 'CA00', 'CA01', 'CA03', 'CA04', 'CA02', 'PA00', 'PA01', 'PA02', 'PA03', 'PA04' }; var maxStateChangeMonth = 4; var excludeStatusId = 999; var SumOfMonthlyPayments = context. Accounts . Join(context.

AccountOwners, a => new { CreditorRegistryId = a. CreditRegistryId, a. AccountNo }, ao => new { ao.

CreditorRegistryId, ao. AccountNo }, (a, ao) => new { Account = a, AccountOwner = ao }) . Where(x => x.AccountOwner.

AccountOwnerRegistryID == ownerRegId &&!excludeTypes. Contains(x.Account. AccountType) && (x.Account.

StateChangeDate == null || x.Account.StateChangeDate. Month - DateTime.Now. Month Math.

Abs(x.Account. MinimumInstallment)); And here it is without explicitly using the join: var ownerRegId = 731752693037116688L; var excludeTypes = new { 'CA00', 'CA01', 'CA03', 'CA04', 'CA02', 'PA00', 'PA01', 'PA02', 'PA03', 'PA04' }; var maxStateChangeMonth = 4; var excludeStatusId = 999; var SumOfMonthlyPayments = context. AccountOwners .

Where(ao => ao. AccountOwnerRegistryID == ownerRegId &&!excludeTypes. Contains(ao.Account.

AccountType) && (ao.Account. StateChangeDate == null || ao.Account.StateChangeDate. Month - DateTime.Now.

Month Math. Abs(ao.Account. MinimumInstallment)).

Hi Jeff: Thanks for the response. Can I do it without join using the navigation property? – DotnetSparrow Apr 3 '11 at 9:26 @Sparrow: Sure it can be written without using join explicitly, though I don't know what you mean by the navigation property.

I just saw "need this SQL-ish query converted to LINQ. " I don't really have too much experience working with EF. – Jeff Mercado Apr 3 '11 at 9:34 I mean because these two entities have related properties accountowners has account property and account has accountOwners property.

They can access each others columns without join. – DotnetSparrow Apr 3 '11 at 9:48 @Sparrow: That I didn't notice. However I think rewriting it to use that property may be pointless as the query provider will probably write it back to using a join anyway.

– Jeff Mercado Apr 3 '11 at 20:19.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.