CODEWITHSUNDEEP
entity-frameworkentity-framework-core
user4266661
user4266661

Reputation:

EF Core FromSql Returning Odd Error Message

This code throws an exception when rawVoters.Count() is called:

 string sql = @"select * from Voters as v
 inner join Homes as h on v.HomeID = h.ID
 inner join Locations as l on h.LocationID = l.ID
 inner join Streets as s on l.StreetID = s.ID
 inner join Cities as c on s.CityID = c.ID
 inner join VoterAgencies as va on v.CountyID = va.CountyID and v.VoterID = va.VoterID
 where (va.AgencyID = @agencyID)
  and (c.Name like '%' + @city + '%')
  and (v.FirstName like '%' + @firstName + '%')
  and (v.LastName like '%' + @lastName + '%')
  and (s.Name like '%' + @street + '%')
  and ((@voterID = 0) or (v.VoterID = @voterID))";

            List<SqlParameter> parameters = new List<SqlParameter>();
            parameters.Add( new SqlParameter( "@agencyID", agencyID ) );
            parameters.Add( new SqlParameter( "@city", model.City ) );
            parameters.Add( new SqlParameter( "@firstName", model.FirstName ) );
            parameters.Add( new SqlParameter( "@lastName", model.LastName ) );
            parameters.Add( new SqlParameter( "@street", model.Street ) );
            parameters.Add( new SqlParameter( "@voterID", model.VoterID ) );

            IQueryable<Voter> rawVoters = _context.Voters
                .AsNoTracking()
                .FromSql( sql, parameters.ToArray() );

            int numVoters = 0;
            try
            {
                numVoters = rawVoters.Count();
            }
            catch( Exception e )
            {
                int i = 9;
                i++;
            }

The error message is:

"The column 'ID' was specified multiple times for 'v'."

I thought this might be because EF Core doesn't like the "as x" phrasing, so I substituted the table names for each of the identifiers...and got the same error message.

I'm curious as to what's going on here.

Upvotes: 1

Views: 321

Answers (2)

user4266661
user4266661

Reputation:

The problem was that the T-SQL was returning all fields (select *). Under EF Core, the returned fields must match the fields specified for the entity being returned, in this case Voter.

An inner join, like the one I was using, by default returns far more than just the Voter fields.

Changing the SQL to be select v.* (where v is the alias for Voters) solved the problem.

Upvotes: 1

Sampath
Sampath

Reputation: 65870

Hence you're just getting the Count,you can specify your column name as shown below.Then no issues :)

string sql = @"select v.ID from Voters as v

Upvotes: 0

Related Questions